Linked Forms Calculation

Viewing 2 reply threads
  • Author
    Posts
  • February 28, 2019 at 8:25 AM #33882

    John Simpson
    Participant

    I need help with what seems like a simple process, trying to calculate a cost with data from 2 forms.

    The first form has records of inventory, each record holds data on one type of filament and includes the calculated cost per millimetre.

    The second form has records of jobs and includes the type and length of filament used per job.

    I want to multiply the cost per millimetre by the length of filament type used per job. Everything I’ve tried just offers the inventory Total like this:
    Filament Used (mm) * TOTAL( Filament::Cost per mm )

    How can I pick the specific inventory record to extract the cost per mm?

    February 28, 2019 at 11:01 AM #33883

    Brendan
    Keymaster

    Hi John,

    Tap Forms is doing the TOTAL() because you have a Link to Form field there and it doesn’t know which record to use, so the only choice is an aggregate calculation for the field from all the linked records.

    You’ll have to add your Calculation field to the Filament form instead. Make sure you have the “Show Inverse Relationship” option enabled. Now, if the relationship between your forms is a one-to-many then on your Filament form you can access directly the parent field’s Filament Used (mm) field to do your calculation. But if it’s a many-to-many or a Join Link Type, then that won’t work.

    With a Form Script or a Script Field you could write some JavaScript to pick out an individual record from the Cost per mm Link to Form field and then use that value in your calculation.

    Thanks,

    Brendan

    February 28, 2019 at 3:35 PM #33887

    John Simpson
    Participant

    Brilliant! Thank you! It’s working just fine now.

    Database relationships make my head spin :-(

Viewing 2 reply threads

You must be logged in to reply to this topic.