1 2 Previous Next 16 Replies Latest reply on Apr 22, 2017 11:02 AM by oneillrj@pbapi.com

    Let's build a ForEach Loop


      I need to update data in one table from updated data in another table.


      I have a master list of manuals with revisions. I also have a list of instruction forms which require a list of manuals. These forms and manuals are unique based on the item being worked on. So the same instruction list will be the same for each of the same item. But not all items are the same and the different ones will have a different set of manuals.


      When the manual revision changes I need to reflect this on the instruction form when the new form is printed.


      So, what I've done is to create the master table of manuals with revision (versions), date of revision and a concatenated field of Manual/Revision. When the revision of the manual changes I want to create a script which will then change all instances of the same manual/revision (concatenation of Manual and Revision) based on the new data. The changes will take place in the Manuals instruction form table.


      Yes, two tables have the same data. Yuck..... but this is necessary because I must be able to refer to the past instruction form's manuals when it is from a later date and it must show the version of the manual used at that time. The changes must be reflected on any 'jobs' after the manual has changed.


      OK now to what I've done... I have successfully been able to change the data in the Manuals on forms table through a find and replace contents script step.


      What I want to do is avoid writing the same script steps 35 - 50 times one for each different manual for each different form.


      I thought it might be possible to create a 'list of fields' which could be in a container of sorts. Then using a Loop, loop through each of the 'contained' fields to perform the necessary script steps. In C# it is simple to perform a ForEach to iterate through the contents of a container.


      I can't find the equivalent 'tool' in Filemaker Pro 15.  I either have to execute the same script for each of the fields in the table that need to be changed or I come up with a way to loop through all the fields in the table and perform the same script for each field.


      Any thoughts?

        • 1. Re: Let's build a ForEach Loop

          and a concatenated field of Manual/Revision.

          Speaking as someone who once managed documents for an ISO certified document control system, this does not seem the right approach. I would replace that field with a related table of versions. Current Instruction forms would reference the most recent version record in this table. Past versions of the form can use a different match field to record the version in effect at the time the form was updated and replaced with a new version.


          With this approach no need exists for the type of Batch updating that you want to do here.


          That said:

          I thought it might be possible to create a 'list of fields' which could be in a container of sorts.

          Either a variable or a text field can be set to a list of values. These might be a list of Primary keys pointing to records in a particular table. Not sure here why you call it a "list of fields" here. If you really want a list of field names, that suggests a far from ideal data model. Such a list can be created in a number of ways: The List function, ExecuteSQL, there's a "list of" summary field all which might produce a return separated list of values. Such a list can be used with a Relationship and Go TO Related records to create a found set of records to update or you can use a looping script with GetValue and a loop counter variable to link to and update one record at a time in a loop. Both methods can encounter problems if you run the script at a time when other users might have one of the records you want to update open for editing as that will lock your script out of updating that record--another reason to avoid this approach if it is not necessary.

          • 2. Re: Let's build a ForEach Loop

            There's no explicit 'for/each' so we generally use a counter, e.g.:


            Set Variable[ $count ; ValueCount( yourList ) ]
            Set Variable[ $i ; 0 ]
              SetVariable[ $i ; $i + 1 ]
              Exit Loop If[ $i > $count ]
              Set Variable[ $item ; GetValue( yourList ; $i ) ]
              // do your process here
            End Loop
            • 3. Re: Let's build a ForEach Loop



              Thank you for your candid views. I appreciate your willingness to help.


              How can I accomplish the following using as little script as possible?


              Each work order has a 'final' document which must list each of the manuals used. No duplications of manuals even though more than one instruction form may reference the same manual. In other words, if more than one instruction form uses the same manual, that manual must be mentioned only once on the final document.


              Each instruction form is created for each model being worked on based on the work being performed. Let's say that a model will be overhauled. A set of manuals is indicated some for each instruction form and it is possible that more than one form will reference the same document.


              If the work is started using a manual set and one of those manuals is updated between the start of the work and the final document or work order completion, I must notify the person preparing the final document that a manual has changed and allow them to decide how to handle the situation. I must do this without changing the manual revisions on the forms or the final document unless the person finalizing the paperwork indicated that the latest versions should be used.


              My plan was to have a master manuals/revision/date of revision table which can be updated. Also, I planned to have a table with the model and work to be performed table which copied the necessary forms from the master manuals table as indicated by the technicians. This table would have a date to indicate when the last update to the instruction form's manual set was made.  I was going to script a concatenated string of all forms filtering out (using script) duplicates. This string would have a date of last modification as well to be used when finalizing the paperwork to compare the date of the initial data with that of the versions when the work order is finalized to insure that no modifications have been made. If modifications or revisions have been made the person doing the paperwork would see an indication and make a determination if they should change the versions or not.


              On the main work order I was planning to copy the concatenated instruction form manuals (collection of manuals) for each instruction form as well as its last revised date to be used for comparison. Also, I planned to copy the string of manuals where there are no duplications for the 'final' document. The associated dates would be used for comparison.


              If there is a way to relate all this using relationships and minimal scripts that would make things simple.  I have been working on this for a few days and have not yet come up with a solution. I can't see how to relate the information forms manual list to the master list.


              In summary, the instruction form table has a record for each model/work to be performed. The master list is only the manuals and versions with revision date a calculation resulting in the manual and revision as one field. This field is used on the instruction form.


              When I said in my original post that I wanted to iterate through the fields collection I meant field because unless I can come up with another way, I will need to change the manualRev field for each record for all fields that contain manualRev data that has changed. I realize that there are records that need to be changed but it is the fields within the record which holds the target data.


              Using the following script I am able to change the field in the found set with the proper Manual/Rev data that has changed.





              Enter Find Mode [Pause:Off]

              Set Field [Information Forms Table::AHO1;$$OldManualAndRevision]

              Set Error Capture [On]

              Perform Find[ ]

              Set Error Capture [Off]


                   Set Field [Information Forms Table::AHO1;$$NewManualAndRevision]

                   Go to Record/Request/Page [Next; Exit after last]

              End Loop

              Show All Records





              This works and changes all the records in the Information Forms table for that one field where the manual data has changed. But, I must also check every other field to search for the same $$OldManualAndRevision and update that field for that record.


              That is why I originally asked about looping through the fields so that I could search each field to 'find' each record where that field has the old date to allow me to replace it with the updated data.


              If you don't mind perhaps we can discuss an alternative. I would most happy to learn a technique which would allow me to accomplish what I need to do without this script.


              Manual revisions may happen a few times a month and may not effect many Instructional Forms but when they do they must be updated and I need to know what it was before and what it is now when the final documents are prepared.

              • 4. Re: Let's build a ForEach Loop

                Thank you for this script. If I can't find another way (see the other posts) perhaps I can discover a way to create a 'collection' or list to iterate through which contains the target fields.

                • 5. Re: Let's build a ForEach Loop

                  Your current data model and relationships are not clear. Nor is each document class. Is this what you have?


                  WorkOrders----<InstructionForms-----<Form_Manual>-----Manuals   (---< means "one to many" )


                  So a given WorkOrder consists of one or more instruction forms and each InstructionForm links to one or more manuals and a manual can link to one or more instruction form? And in the Manuals table, you can have separate records for different revisions of the same manual?

                  • 6. Re: Let's build a ForEach Loop

                    I think so.


                    The work order record prints one or more instruction forms (layouts within the Work Order table).


                    The instruction forms layouts display/print the manuals associated with them.


                    The master manual list is in a table with no current relationship to anything as I can't seem to wrap my head around the key which would make that work. There doesn't seem to me that there are any 'related or common' fields that I can find between the master list, the work order or the instruction forms manuals table.


                    There are multiple records for each 'model/work to be performed' in the instruction forms manual table as the main identity for that record. Each record contains manuals used on the forms based on a unique 'model/work to be performed'.


                    So the master manuals table doesn't relate to the model/work field nor does it relate to the work order. It's simply a list of manuals and revisions.


                    The Master Manual list table cannot have more than one revision. It is the table which is kept current. When a manual changes those changes must be propagated to the Instruction Forms Manual table but not in the data currently stored in the work order. When the final documents are prepared a check is made to see if the data of manuals/revisions that were used when that work order is started is different that the current data. If it is, the person doing the final documents is alerted and makes a decision to either allow it to be printed using the original manualRev data or to update the work order manualRev data to reflect the current revisions.


                    Does this make sense?

                    • 7. Re: Let's build a ForEach Loop

                      I suggest posting a screen shot of your relationships graph. What you describe doesn't really confirm that you are using relationships patterned after what I outlined or not.


                      I suspect that a major issue here is the lack of a join table between the master manuals table and the other tables in your database.

                      • 8. Re: Let's build a ForEach Loop

                        I'm with Phil on this. Please post a screenshot of your graph. I may be that you need a "join" table between two tables that you do not have. A join table will have it's own primary key, of course, but it's the foreign keys that "link" other tables to each other. Because of it's location, the join table can help "tunnel" data between whatever tables it links (or joins).


                        parent -< child -< grandchild

                             parent::parentID_pk = child::parentID_fk

                                       child::childID_pk = grandchild::childID_fk

                        while not the 'join' you need, you can see parent fields from: child AND grandchild. you can see grandchild fields from child AND parent.

                        from whichever context (layout/table occurrence) you can report and use the fields "as is" or in calculations in the other tables, due to these relationships (even more than one away).


                        "B" is a 'join' (and has a B::bID_pk) in addition to these relationships:

                        A::aID_Pk = B::aID_fk

                        >     B::cID_fk = C::cID_pk

                        "A" can still see information in "C" that would tunnel through "B", etc. But only the information that would be related. If there are no records in "B", then "A" will not have any links to "C".



                        • 9. Re: Let's build a ForEach Loop

                          Thanks to everyone for continuing to help me with this.


                          The relationship is simple. The main work order table has a field named 'ModelWork' which identifies which Model and the work that is to be performed. This field matches the field in the Information Forms table which in turn manages the data which identifies the manuals for each form. When a work order is created and the Model is entered and the work type is entered, the ModelWork field is created and when the Information Form is printed, the related manual list is printed with the appropriate form.


                          I did however remember that I left one thing out. My apologies ...


                          The Main Manual List table exists as a way to populate the drop down box for the manuals on the Instruction Form manuals layout. It is this table where old records must be updated when a manual is 'revised' in the Main Manual List table.


                          All I'm trying to do is some how get the manual list for each Information Form updated when the Main Manual List is revised.


                          There is no relationship between the Main Manual List and any other table as it is used to populate the drop down for the selection of manuals on 'new' ModelWork records for the Information Forms table.


                          But... after reading about the tunnel I'm confused (of course ).  How are all the existing 'old' records updated through the tunnel?

                          • 10. Re: Let's build a ForEach Loop

                            to repeat: Please post a screen shot of your relationships graph.

                            • 11. Re: Let's build a ForEach Loop

                              OK Here you goCapture.JPG

                              • 12. Re: Let's build a ForEach Loop

                                I realize the screen shot of that part of the relationships isn't much help.


                                To clarify, the manuals on work order table has fields for the manual names used for each different information form. So, there isn't a field to relate to the Main Manual list (propeller manuals).


                                Perhaps I need some guidance in restructuring the tables to allow for relationships and easier updating.


                                The problem is that while the Instruction Form may be common, depending on the ModelWork the manuals may be different.

                                • 13. Re: Let's build a ForEach Loop
                                  OK Here you go

                                  Really? No, that's not much help.

                                  But it does show a classic design problem: numbered fields.





                                  Perhaps I need some guidance in restructuring the tables


                                  • 14. Re: Let's build a ForEach Loop

                                    This doesn't look the all the tables needed for what you described earlier.


                                    Recall this from an earlier post?




                                    I don't see a table for your InstructionForms.


                                    And note the join table Form_Manual. This facilitates a many to many relationship between instructionForms and Your master table of manuals. It allows one manual to be linked to many different InstructionForms and one InstructionForm to be linked to many Manuals.

                                    1 2 Previous Next