1 2 Previous Next 21 Replies Latest reply on Sep 11, 2015 6:42 AM by Mike_Mitchell

    Loop to Create set of Records


      I am building an application to help manager our Homeowner's Association.


      My question is about scraping the process of creating a batch or new "Line Items" that can then be assigned to invoices.


      I would like to automate the process of creating invoices for our members. Twice a year, I have to send Invoices to all members of our association, based on the number of Lots they own in the neighborhood.


      Most members own one LOT but several own more than one.


      TABLE Structure


      • ACCOUNTS (Who we bill)
      • CONTACTS (People including one marked as "Primary" whose address and contact info is in ACCOUNTS
      • LOTS (Lot ID, Lot Address, ID_ACCOUNT of current owner.)
      • INVOICES
      • PAYMENTS (future. record of all checks received. )
      • JOIN_PAYMENTS_ALLOCATION (future. Join table to allow split of payments across invoices)


      We bill our members once in July and once in February. The July invoice is for Annual Dues. The February invoice is for Road Maintenance. Billing is based on the number of lots owned. A line item on a typical invoice would be "Annual Member Dues, 2015-2016, Lot #1".


      Members that own multiple lots would receive an invoice with a line items for each lot they owned and a total.


      For each LOT, I want to create a new record in the BILLABLE_ITEMS table populating the fields shown below.


      • ID_Billable_Items (auto enter serial number)
      • fk_ID_Lot
      • fk_ID_Account
      • Fiscal_Period (text) (e.g. 2015-2016)
      • Description (text) (e.g. Annual Member Dues or Roadway Assessment)
      • Invoice_Description (calc concatenating fk_ID_Lot & Fiscal_Period & Description


      I have no experience with looping scripts. How would I construct a script to loop through each record in LOTS to create a new record BILLABLE_ITEMS?


      Once the new batch of Billable Items is created, I want an "easy way" to create an invoice - assigning each new Billable Item to an Invoice with the proper Account, formatting as PDF, emailing to the email in ACCOUNT and (I think) saving a copy of the pdf in a container field in INVOICES.


      Any help would be appreciated - especially on the looping script as that's my current road block.

        • 1. Re: Loop to Create set of Records

          In this particular case, given the schema you've outlined, I don't believe a loop is needed. You can simply import the found set from LOT into BILLABLE_ITEMS. Importing is vastly faster than trying to loop over the source set and create child records one at a time. I'd do something like this:


          1) Find the desired records in the LOT table.

          2) Go to a layout based on BILLABLE_ITEMS.

          3) Import ID_Lot into fk_ID_Lot and ID_Account into fk_ID_Account. (Allow ID_Billable_Items to auto-populate.)

          4) Use Replace Field Contents to populate Fiscal_Period and Description.

          5) Invoice_Description will populate on its own.


          Now, this does have a couple of issues associated with it. First, it's not particularly multi-user safe, so if you have multiple people who might perform this operation simultaneously, you might want to head off by setting a flag at the beginning of the script and clearing it at the end. Another issue is that Replace Field Contents, while very fast and efficient, is poor for error trapping purposes. If there's an error, you won't know which record(s) failed. You just know one or more did. You can somewhat compensate for that in this case by hunting for empty Fiscal_Period and Description values and trying again, or you can go with Set Field and a loop over the imported records.


          All depends on how robust it needs to be, depending on the environment. If it's an HOA, you probably only have one user at a time, which means you really don't have these concerns too much. You can pick up more sophistication as you learn.





          • 2. Re: Loop to Create set of Records

            Mike's way is sound, you might want to learn another technique here:


            NightWing Enterprises - Duplicate Hierarchy v2.0 for FileMaker Pro 13

            • 3. Re: Loop to Create set of Records

              We've done something very similar for a few of our clients over the years. I agree that importing and replacing field contents might work, but the caveats would make me a little gun-shy in your scenario (which is why we loop).


              We usually create two windows with specific names and off the screen of the user. The first window would be your lots and the second would be the invoice. Sort the lots by the owner, and track their ID as a variable. When reading in the next value, compare the new with the previous ID to see if a new invoice is required, otherwise the next lot is the same owner. In one of our solutions, we have the script performed on the server to loop through, creating a new invoice line item with the invoice number and date (along with whatever other data you would like to have as part of the line item). This makes the creation very quick. Then we close those two windows, and when ready to bill, we perform a find based on the invoice date, sort and group and PDF the invoice to the appropriate owner.




              • 4. Re: Loop to Create set of Records

                If you really want to make something "bulletproof", I wouldn't mess with multiple windows. Instead, I would use a transactional technique. Todd Geist is sort of "the guy" in this area; you can read about on his web site here:




                Short version: Use portals "off screen" (outside the layout area) and keep all the records open until you've completed the entire operation. If any operation returns an error, revert the record to cancel out everything. If everything works as it's supposed to, commit the record to save all changes.


                By using a transactional approach, you avoid creating any orphaned records if you receive an error (like a record lock conflict, duplicate, or any of the other various errors that can occur during an operation like this). You also can pass the operation off to a server-side script using PSoS without having to worry about windowing considerations (several of the windowing script steps are not server compatible and will create errors in the log if you don't suppress them). Finally, since you're already working through relationships, it's very easy to check for the presence or absence of needed records (to determine the need to create new ones).

                • 5. Re: Loop to Create set of Records

                  Thank you for the reply and suggestion.

                  • 6. Re: Loop to Create set of Records

                    Thank you. I was originally planning to take the looping approach. I was looking for some guidance on how to write the looping sequence.


                    But, I hadn't even considered the Import technique mentioned by Mike Mitchell. In my situation, I think the import method will work fine.


                    Thanks again for taking the time to reply.

                    • 7. Re: Loop to Create set of Records



                      I hadn't even considered this but it seems like a better idea in my situation. Thank you.


                      I'm going to give it a try.

                      • 8. Re: Loop to Create set of Records

                        Hi Mike,


                        I am trying to implement a transnational approach with Selector Connector using the "Set Field" script step. I am able to create a new related record using the second technique (without portals) described by Todd here: http://www.modularfilemaker.org/module/transactions/


                        This is great because I don't have to leave the layout!


                        However when I go to loop through the creation of a second related record during that transaction the script rewrites the data for the fields I want to enter over the data in record I just created. It's as if it never goes to create a new record. I have some calculations setup to watch the number of open records and it is not exceeding 1 open record. The first time I did it I was able to get it to work, but I am not sure what is going wrong now. I am clearing the global variable on the left side of the relationship by setting it to "" after a record is created via the script. Any suggestions?



                        • 9. Re: Loop to Create set of Records

                          Commit the record.

                          • 10. Re: Loop to Create set of Records

                            I thought one of the main reasons for doing record creation this way was to avoid committing the record. I need all of the records to commit or if something fails none of them to commit.

                            • 11. Re: Loop to Create set of Records

                              After all of the new records are created then a single script commit would commit them all at once. If an error occurred during creation they could all be reverted.

                              • 12. Re: Loop to Create set of Records

                                You need to use a portal in order to implement a transactional method. You can then go to the next portal row to create the next record in line.

                                • 13. Re: Loop to Create set of Records

                                  tm9 wrote:


                                  I thought one of the main reasons for doing record creation this way was to avoid committing the record


                                  Right, forgot about that.


                                  If you have a problem with a script, it's best to show that script (even better, paste it as text, not as screenshot).

                                  • 14. Re: Loop to Create set of Records

                                    I tried copying and pasting the script to text, but I could not get it to work. I have attached pictures of two scripts that do similar things.


                                    Sample Script 1 is a simplified version of Sample Script 2. Script 1 just creates the product records where script 2 has some basic field validation and creates the relationship between the newly created product and the store.


                                    I have attached a screenshot of the relationship graph and the layout. Click the + runs the script to add to create a new record in the Product table for the name entered in the global field that is shown on the layout. I have two global fields to simulate adding more than one record in the product table during a single transaction.


                                    Mike - I want to be able to add multiple new records in the product table without using a portal. In a real world scenario I would like to utilize this technique with a picker window adding new records using data generated in a value list.


                                    Sample Script 1

                                    Create Product Sample Script 1.JPG


                                    Sample Script 2

                                    Create Product Sample Script2.JPG

                                    Relationship Graph


                                    Relationship Graph.JPG

                                    Store Layout

                                    Store Layout.JPG

                                    1 2 Previous Next