11 Replies Latest reply on Oct 31, 2016 2:20 PM by tony-v

    How do I check the status of the related records?

    tony-v

      I’m well new to FileMaker.

      I have  two tables: Projects and ProjectLineItems. One Project record is related to many ProjectLineItem records. Each of the tables have a ‘Status’ field, where the status of the project or line item is selected.

      How can I make the status of the project change automatically to ‘Complete’ when the status of all the related line items has been set to ‘complete’?

       

      Many thanks in advance.

        • 1. Re: How do I check the status of the related records?
          beverly

          welcome, tony-v!

          that's the trick isn't it? ALL the related line items are complete?

           

          you can Count() the related line items (on the foreignkey perhaps) and Sum() the related status field (a number field of 1 or 0?). if the two are the same, then the parent would have ALL related line items complete.

          in Project:

          if ( Count( ProjectLineItem::foreignKey ) = Sum( ProjectLineItem::Status ) ; result here )

           

          The trick may be to get a calculations such as this to update. You can try to see, I'm just guess here.

          beverly

          • 2. Re: How do I check the status of the related records?
            David Moyer

            Hi,

            I can't find the thread that's covered this before, but there's a clever way of using functions, instead of looping through child records.  If ...

             

            Let(

              [

                paddedList = "¶" & Substitute(List(relatedTO::Status); "¶"; "¶¶") & "¶";

                status = "¶Complete¶";

                statusCount = PatternCount(paddedList; status);

                recordCount = Count(relatedTO::PrimaryKey)

              ];

              statusCount = recordCount

            )

            ... will return 1 if all child records are Complete, 0 otherwise.

            p.s. all the extra ¶'s are there to prevent counting multi-word statuses like "Not Complete" or "Complete Pending".  I haven't tested this for syntax.

            • 3. Re: How do I check the status of the related records?
              David Moyer

              I guess I assumed your status field is text.

              • 4. Re: How do I check the status of the related records?
                beverly

                That was a little vague for me, too, David!

                 

                another option would be ExecuteSQL(), but I would not use it as a calculation that needed update frequently (every time the status changed).

                 

                beverly

                • 5. Re: How do I check the status of the related records?
                  David Moyer

                  one more thing - you probably need to account for zero child records.  Otherwise, you might get a 0=0 comparison, which is True.

                  • 6. Re: How do I check the status of the related records?
                    beverly

                    yes, all children would be completed if there are none! good catch, tho.

                    • 7. Re: How do I check the status of the related records?
                      tony-v

                      First off, thank you for all your answers and you time.

                       

                       

                      David, your assumption is correct, the status field is text indeed. And the solution you proposed is operational however not in full.

                       

                      There are two different layouts. One of those is Project Details which is based on Projects table, the second one is ProjectLineItem layout based on ProjectLineItems table.

                      As said before, one project record is related to multiple ProjectLineItem records.

                      I tried to trigger the script (on objectValidate) when changing the status field on ProjectLineItem layout. However, it seems that FM cannot figure out which Project the Line Item is related to, as well as the total number of line items related to the Project. At the same time, when triggering the script when on the Project layout, it works as it should.

                      • 8. Re: How do I check the status of the related records?
                        electon

                        So you have separate layouts for Project Details an Project Items.

                         

                        You can't use the List ( field ) function because it will work only if LineItems are in a portal on ProjectDetails layout.

                         

                        You can create a summary field ListOf ( yourStatusField ) in the ProjectLineItems table.

                        Set up, for example an OnObjectSave trigger on the LineItem status field that points to a script.

                        The choice of trigger is up to you, could be on record commit.

                         

                        The script needs to look for one uniform value ( all items have same status ), so:

                         

                        The summary field will give you a value list of all status fields.

                        Filter this list out looking for your specific status:

                         

                        SetVariable [ $filtered ; FilterValues ( summaryField ; "Completed" ) ] ;

                        and then compare to number of records to number of values returned by the filter:

                         

                        If ( ValueCount ( $filtered ) = Get ( FoundCount ) ; "All records have same status you were looking for" ; "Else they don't " )

                         

                         

                         

                        HTH,

                        Thomas.

                        • 9. Re: How do I check the status of the related records?
                          tony-v

                          Indeed, there is a portal on the ProjectDetails layout listing the LineItems records pertaining to the Project. The List (Field) function works well in such context. However it doesn't work on the LineItem layout.

                           

                           

                           

                          When on the LineItem layout the ‘List of’ function finds all the line items records rather than only those which belong to one individual project. Therefore, this function returns the list of statuses for all the records contained in the LineItems table. The same goes for the Get (FoundCount) function. In the context of LineItems layout it returns the total number of all records found in the LineItems table.

                           

                          When I go to the LineItem layout and change the status of the LineItem record to ‘Complete’, I need FM to understand which Project this given LineItem belongs to, following which find all the other LineItem records related to that Project, check if all of them have Complete status and if Yes change the status of the Project to Complete.

                           

                           

                           

                          Regards,

                          Anton

                          • 10. Re: How do I check the status of the related records?
                            electon

                            tony-v wrote:

                             

                            When on the LineItem layout the ‘List of’ function finds all the line items records rather than only those which belong to one individual project. Therefore, this function returns the list of statuses for all the records contained in the LineItems table. The same goes for the Get (FoundCount) function. In the context of LineItems layout it returns the total number of all records found in the LineItems table.

                             

                             

                             

                            Regards,

                            Anton

                             

                            When doing this from the LiineItem layout context, you can set up a self - join relationship by project id.

                             

                            Just link another table occurrence of the LineItems to this one by matching project id key field

                            That relationship will give you all LineItems for the selected record's project.

                             

                            Then you can use, for example:

                            List ( lineitem_lineitemByProjectId::Status )

                            Same goes for Count ( related records )

                            • 11. Re: How do I check the status of the related records?
                              tony-v

                              I finally managed to get it to work. Many thanks for your comments and your time.