10 Replies Latest reply on Oct 20, 2016 9:09 AM by philmodjunk

    Embed If in a loop


      I have a simple script that creates invoices for student tuition. Some students have a line item for tuition and a line item for the after school program. The first student (as the sort would have it) is for a student that only has a tuition line on the invoice, the invoice is created properly, and the script advances to the next student and creates an invoice. However, the second student DOES have an after school program, it is added properly to the invoice, BUT the script continues to create invoices for that same (the second student) over and over until I stop the script. The first invoice is correct, the second invoice is correct, and then all of the other invoices created are replicas of the second one (except the invoice number advances).


      It appears that when the student does have the after school program item to be billed, then the script does NOT advance to the next student.


      Below is the script in two versions. One with an Else If (I was trying) and one without Else If. Both result exactly (as far as I can tell) as I have described above.


      Please help if you can.

        • 1. Re: Embed If in a loop

          I am not a big fan of using go to portal row within a script to process and/or create data from a related table. It's vulnerable to a number of potential issues.


          But the looping logic looks to be OK as far as I can see from here (which isn't far).


          If you have FileMaker Advanced, I suggest enabling the script debugger and running this script while watching the script (and use the data viewer to watch the data in fields and variables) to see where and how it goes wrong.


          If you don't have advanced, beg, borrow or steal the funds to get a copy. For a script like this, the debugger alone will save you hours of frustration when things don't go as expected.

          • 2. Re: Embed If in a loop

            What would you recommend instead of going to a portal row within a script to create data from a related table? For my knowledge, what are the potential issues?



            • 3. Re: Embed If in a loop

              go to portal row has to specific points of potential failure:


              There's no parameter in the step to use to specify which portal on your layout it is intended to interact with. This requires using Go To Object (or go to field, but Go to Object is the better choice), to set the focus on the desired portal.


              It interacts with data in your tables via specific, direct interaction with an object on your layout (the portal), not only can a script using this step fail if the object were removed, the script step changes focus to a different part of your layout and this can trip script triggers. Thus, a future edit to this layout that adds a script trigger can screw up your script just as removing the portal from the layout would cause it to fail. In addition, it's fairly easy to lose track of object names that are used in a layout and accidentally change or remove one without realizing that you've done so--with potentially catastrophic consequences for your go to portal row script.


              There are two basic alternatives for working with related data that are commonly used in FileMaker:


              Pull up a found set of the portal's records on a layout based on the portal's table and loop through the records instead of portal rows. Often, this is done in a new window that is hidden off the edge of the screen and closed once the script is through looping through the records. Go to Related Records can open such a window and produce the needed found set all in one step.


              Use "MagicKey", this is a name you can web search to learn more. It uses a relationship in a rather unique way to enable the creation of related records without changing layouts and without looping thru portal rows--thereby avoiding the tripping of script triggers that can happen with either of the other two options. Sometimes, ExecuteSQL is used in conjunction with this method to produce a list of primary keys for use with Magic Key in a loop to edit existing data.

              • 4. Re: Embed If in a loop

                philmodjunk wrote:


                There's no parameter in the step to use to specify which portal on your layout it is intended to interact with. This requires using Go To Object (or go to field, but Go to Object is the better choice), to set the focus on the desired portal.

                See step 19 in the script. It does specify which portal on the layout to interact with.


                The overall process does rely on using a layout that lists the invoices (based on student enrollment -> billing entered into another table). In that way, if a student's enrollment status changes (amount to be invoiced changes) the billing would change automatically. That is a part of the overall process that I did not share. I just shared the portion that I'm trying to get FMP to do for us.


                Right now, we are only creating about 120 invoices once per month using this method. If the script failed because the portal was removed or it's name was change, the user would know there was an issue when he/she review the records (invoices) created with the list of those that should be created.


                To anyone reading this at another time, I would like to be clear that philmodjunk's response is entirely appropriate and correct. This method relies upon my knowledge of the how scripts, object names, etc affect the functionality of this script. I am not building this solution for another entity. I am building it as part of my job and I fully understand the goal of the script and the overall process and have built in other steps (manual) to mitigate the issues he raises. Furthermore, I use a table in each FMP solution that I create to track objects that are named, their location, why they are named, and what changes must be made in other places should an object's name be changed. Again, it is not a solution for these issues that is appropriate for a solution that I am not monitoring or that I am creating for another organization.


                Thanks, philmodjunk!


                Regardless, my script still does not work as I would like it to . . .

                • 5. Re: Embed If in a loop

                  I removed the If statement entirely. So, now if a student does NOT have a billing item for after school program, then a blank line (record in lines table) is created. Interestingly, the script behaves in the exact same way: the first two invoices are created correctly, then the script continues to create new invoices identical to the second invoice (except that then invoice number is incremented).

                  The problem is not (or not entirely) with the If statement.


                  See attached screenshot of updated script with the If statement removed.

                  • 6. Re: Embed If in a loop

                    On line 21 and 24 you write to an invoice line item record and try to pick up a value from the SIS table.  Your context however is still on the new invoice record.  Is that relationship to SIS valid and correct?

                    Do you see the proper SIS value when you step through it with the Script Debugger?

                    • 7. Re: Embed If in a loop

                      Apologies. I should have pointed out that your If step isn't the problem. You have what looks like the correct code here to do what you want. Your Go to Next record step, with the "exit after last" step should be what controls your loop here without any additional modification. Since there's no obvious error, I suggested that you run your script in the script debugger.

                      See step 19 in the script. It does specify which portal on the layout to interact with.

                      Of course it does, but that's my point exactly. You have to add that step in and in doing that you a) change layout focus--which might trip script triggers and b) this is future point of possible failure should that object name get changed or lost during a future layout modification.

                      • 8. Re: Embed If in a loop

                        Yes, I do. thanks for asking. Valid question!

                        • 9. Re: Embed If in a loop

                          Following the advice of philmodjunk, I restyled the script to add data to the Invoice Lines table as opposed to the Invoice Lines portal on the Invoice layout. I got the same result. So, I downloaded FMP Advanced and used the Script Debugger.


                          For the good of all, I will share the issue, which was the issue all along and was not actually due to the If statement in the loop.


                          When the script returns to the Student Invoicing List layout, that layout has a script that is triggered upon entry that finds active students. After the find, the first record is automatically selected, so near the end of the script, it would advance from the first to the second record each time as it was starting on the first record each time the layout was entered.


                          I duplicated the Student Invoicing layout and removed the script trigger (as it was also built into the script prior to the loop). So, now the Student Invoicing layout has two versions: one for people that "automatically" shows the user active students and one for the script that has no script triggers and relies upon the script to perform the find at the correct time (which is one time at the beginning of the script prior to the loop).


                          Thanks, philmodjunk, AGAIN and I hope this becomes useful for others.

                          • 10. Re: Embed If in a loop

                            And I had a private bet just with myself that this was the problem. It wasn't the only possibility so rather than get involved in yet another discussion about script triggers, using the debugger--which has saved me countless hours of frustration and which was a useful tool in learning how script triggers worked when FileMaker first added that feature, was the best bet for finding any of the possible problems with your script.