3 Replies Latest reply on Jun 23, 2015 11:45 AM by ericjlindholm

    calculated field mimic scripting

    ericjlindholm

      Title

      calculated field mimic scripting

      Post

       

      Many on my table relationships are based on fields that would be best managed by calculations.  For example if an Event status is marked declined all related records in other tables would have calculation fields that display the text "declined."  As i understand it, filemaker does not allow you to create relationships based on calculations.  I have a few unreliable script to insert this text into related records fields such as 

      If [ Equipment List::EQ list status = "Declined" ]
      Go to Related Record [ From table: “Line Items”; Using layout: “Quote Input BLANK” (Line Items) ]

      [ Show only related records; New window ] Go to Record/Request/Page

      [ First ] Loop

      Set Field [ Line Items::vlv EQ list Status Declined; "Declined" ] Go to Record/Request/Page

      [ Next; Exit after last ] End Loop

      Close Window [ Current Window ]

      Exit Script [ ] End If

      If [ Equipment List::EQ list status "Declined" ]
      Go to Related Record [ From table: “Line Items”; Using layout: “Quote Input BLANK” (Line Items) ]

      [ Show only related records; New window ] Go to Record/Request/Page

      [ First ] Loop

      Set Field [ Line Items::vlv EQ list Status Declined; "" ] Go to Record/Request/Page

      [ Next; Exit after last ] End Loop

      Close Window [ Current Window ]

      Exit Script [ ] End If 

      Is there a better way to do this or is scripting the only way?

        • 1. Re: calculated field mimic scripting
          SteveMartino

          You should test for related records first before entering your loop (and subsequently skipping it).  Otherwise you may be looping in the wrong context.

          • 2. Re: calculated field mimic scripting
            philmodjunk

            And when pasting script text copied from a PDF, please insert the missing returns so that each script step at least starts on a new line.

            As i understand it, filemaker does not allow you to create relationships based on calculations.

            That's really not the case and I don't see why you need the script or a calculation field.

            If you have this relationship:

            ParentTable-----<ChildTable (---< means "one to many")

            then, if you set a field in ParentTable to "declined". You can easily show all ChildTable records as "declined" without any additional field, relationship or script. If you set up a layout based on ChildTable, you can add the ParentTable::Status field to it and all child records linked to a "declined" parent record will show "declined" in that status field from the ParentTable.

            On rare occasions, you may need to add a calculation field for status to the Child table. In such cases, the resulting unstored calculation field will readily show the current status of the related parent record if you give it this calculation:

            ParenTable::Status

            Which simply copies the value of status from the single related parent record.

            • 3. Re: calculated field mimic scripting
              ericjlindholm

              I do understand adding a test for related records to this script example. Thank you. 

              I would like to correct my statement from my first post. FileMaker does not allow you to create relationships based on unstoraged calculations.

              I tend to use table relationships that would benefit from unstored calculation results.  In the example, ParentTable::Status-----<ChildTable::ParentStatus  I would like ChildTable::ParentStatus to equal ParentTable::Status as soon as ParentTable::Status is modified.  

              So far, mimicking this with scripting has been choice.  I need to clean them up and make them more reliable (first checking for related records will go a long way to that end)

              are there any other ways to achieve this? maybe un checking the "do not replace existing value" option on a fields auto data enter settings?