7 Replies Latest reply on Jul 23, 2010 12:48 PM by philmodjunk

    Looping with script to create records in a table based on looked up criteria in a source table?



      Looping with script to create records in a table based on looked up criteria in a source table?


      I'm having to rework part of database so while I'm at it I thought it would be nice to have some features that I've never figured out how to do in FileMaker.

      A simplified version of what I would like is a script that will;  For every record in TableA that has an indexed Field1 = 123 AND Field2 ="xyz" AND Field3 > 0 create a record in TableB with indexed Field1=TableA::Field2 AND Field2 = to a running total of TableA::Field3 AND Field3= TableA::Field4 

      TableA::Field1 is a key field from a lineitem table

      TableA::Field2 is text that designates a function that is in another table that is used to define the function and the rate for it that is used as a values list for data entry and is not static, meaning new functions are added as needed on the fly during data entry.

      TableA::Field3 is a numerical value, usually time

      TableA::Field4 is an indexed field that relates back to record in a different table that this is a line item of

      I had originally thought I should be able to do this with a summary field but never figured out how to make it work right so I thought maybe a script should be the way to go that I could trigger appropriately. 

      Any Ideas? Pointers? Examples?  

        • 1. Re: Looping with script to create records in a table based on looked up criteria in a source table?

          Making an example abstract generally makes it harder to understand and put together a suggestion. It also prevents suggestions that might give you better results by changing the design of your database. Any time you find you need to copy groups of data from one table to another, it raises the very real possibility that a design change would improve things.

          Sticking with the description you've given...

          1. Define a relationship linking Table A to Table B by Table A::Field2 = TableB::FIeld1. Enable record creation on Table B in this relationship.
          2. Perform a find on table A specifying the three criteria you've posted above.
          3. Start with record 1 of this found set and use Loop to step through each record. You can use steps with
            Set Field [ TableB::Fieldname ; Table A::Fieldname ] to assign the values. The "Allow creation..." option on the relationship will automatically create a matching record if it exists.
          4. Use Go to Record [next; exit after last] to move to the next record or exit the loop if you've reached the end of the found set.

          If you already have matching records in Table B and need new records with the same match value, you'll have to use set variable to capture each of the values and then Switch layouts to the other table, create the new record and then use set field steps to move the data from the variables to the appropriate fields then switch back to the original layout to continue the loop.

          • 2. Re: Looping with script to create records in a table based on looked up criteria in a source table?

            I sure wasn't trying to make it more difficult with my abstraction, quite the opposite really.  If there are more changes I should make now is certainly the time to do it since I learned today that I have to combine several sets of tables to get the invoice to display all the information I want it to.   

            TableA is currently Labor_LineItems, and field1 is f_Labor_ID and field2 is the function that is a kind of trade, (electrician, drywall, plumber, carpenter, etc), and field 3 is the total time worked for that line item.  These are just a few of the fields that are actually in the table and I am currently figuring how I will merge with a materials line items table.  Both of these tables are respectfully linked to material_billing and labor_billing that now will also need to be merged, I'm think of merging them directly into the invoice table.  TableB above is currently the Labor_Billing table and I though it would be nice to have easy summary information available of how much time each trade that worked on project contributed to the total cost of the project and having the information in a table would make it easier to use for a variety of reports that could be made as well as usable in calculations if we will choose to discount/increase an invoice based on one trade's total on that invoice, for example.

            I'm not sure what you mean in step 4 if I already have matching records in TableB and need new records with the same match.  Are you saying that if the script was run once and then run a second time with the same parameters to find the same found set?

            • 3. Re: Looping with script to create records in a table based on looked up criteria in a source table?

              Suddenly this gets all too familiar ;-)

              Let's back up a second and take a look at the basic structure of a typical invoicing system.

              Clients----<Invoices----<lineitems>-----Products     ( ---< means one to many )

              Usually, you have portal to line items on your invoice layout so that you can list each purchased item for a given "sale". The items sold can be physical objects or services such as labor. Either calculation fields in the invoices field using the sum function or Summary fields in line items are typically used to compute invoice totals of the info entered in the line items portal.

              I realize you are trying to intelligently handle details specific to services (labor) and materials, but I'm not sure if you have the above basic structure to your tables and relationships or not...

              • 4. Re: Looping with script to create records in a table based on looked up criteria in a source table?

                Well, my structure is a little different because I'm trying to do more than just invoicing, the goal is total project management.  Some of the functionality is not totally implemented yet and other functionality (payment tracking) is still completely missing and is still being done manually.   What I have right now is as follows.  (not all table instances are shown for line items that are based on ID or a filter and I'm not sure how to show that Projects has all those relations other than on separate lines)


                Clients----<Projects----<Contacts  (very different entity than clients even if they have a couple fields in common)

                Clients----<Projects----<Invoices----<Labor Billing----<Labor Billing Line Items----<Labor Rates

                Clients----<Projects----<Invoices----<Material Billing----<Material Billing Line Items----<Inventory----<Inventory Line Items

                The about is more or less what it looked like several hours ago.  I am currently in the process of creating a new Invoices table that will incorporate the labor and material billing tables.  And the a new invoice line items table for the labor and material billing line item tables.  I'm not exactly sure how I'm going to deal with not all items and labor is necessarily billed for that has been entered in to the system, I had a boolean field for "Invoiced", maybe that will still work. 

                I've used systems that have treated labor as a "non-inventory" item and have disliked them for their feeling that the programmer hasn't spent twenty years in the trades and that labor isn't an "item" and should be handled differently and with much more flexibility.  Project management software usually is really smart about such things as time but doesn't do inventory management well.  Construction materials are not your typical items that are fairly consistently countable.  On jobs material use gets estimated, "four and a half rolls of 20 lb roofing felt", "one quarter tank of propane,"  and the like so I have my inventory infinitely divisible on the fly which I haven't seen much.   I'm trying to take what I've been doing in a combination of programs (Wordperfect, Quattro Pro, and Paradox, Barca, Quickbooks, google map, Project, etc) and put the parts I use and like into one custom made solution that I can continue to tinker with to improve on as time allows and need dictates.  

                I have portals from the Project tab that will go into Material Billing or Labor Billing and entries are made as a project progresses.  This has worked fine.  Now that I want to add invoicing that shows line items I have run into the problem from my other post about reports.  My invoicing layout has tabs for materials to be invoiced and labor to be invoiced on a project.  Sometimes projects are not completed but portions of the labor and materials are billed for, either due to phases or contractual progress payments  or insurance industries stages of completion.  I generally like portals but I have not found a way to have them dynamically size if the record count is greater than I set it for in the layout so I have not tried using it for printed reports, only visual reports where I am analyzing the cost of something or the use of materials, the amount and type of labor, etc.  This post thread is really about getting at some information that I could use for studying projects and the using it for invoicing purposes seems like a bonus and a good idea since that is what part I'm working lately. 

                • 5. Re: Looping with script to create records in a table based on looked up criteria in a source table?

                  More often than not, there are multiple options for how to solve one of these problems. It's your system so you have the final say in how you want to design your system. I'm strictly focusing on the issue of how to structure the invoicing part of your system as that's what this thread is really all about. Thus, the first two lines of your relationship descriptions aren't relevant to this issue. I'm just making sure we're on the same page before I start suggesting something only to find we're going in different directions.

                  Treating labor as a "non-inventory" item is data level design approach that makes handling the data in filemaker much simpler. It need not have that look and feel at the interface level where you can still have separate portals for logging materials and labor if you wish--just to give one example.

                  Now to understand your design...

                  Rows 3 and 4 of your relationships make good sense for the most part and your relationship graph may not look any different when we are finished though the details of how they are related and what data source table each "box" refers to may change. What I don't follow is the purpose of the "Labor Billing" and "Material Billing" tables. What is their purpose?

                  • 6. Re: Looping with script to create records in a table based on looked up criteria in a source table?

                    The Labor Billing and Material Billing Tables essentially hold the individual line items for one invoice under one MaterialBillingID or LaborBillingID with timestamps for when the record was created and modified and links it back to a ProjectID.  So one project may have multiple labor and or material invoices that each may have one or more line items.  

                    The reason that they exist as separate tables and not in the invoice table is simple that adding invoicing is something of an afterthought that I didn't really consider when I originally began putting things together.  Only lately have I been really thinking that I could make a unified system and unfortunately I didn't start out with that in mind having been quite used to doing different things with different software and now that I am moving more toward the mac that doesn't run all my old windows software I'm rethinking the way I've done things and have created this eventual goal of the ultimate total project management that is designed specifically for my small company.  The "plan" as it has been is just to cobble things together, gradually, in my downtime, building and remodeling a solution that works for me and doesn't include all the expensive fluff I wouldn't use that make up the off the-shelf packages which I don't have a history of liking anyway.  

                    • 7. Re: Looping with script to create records in a table based on looked up criteria in a source table?

                      You seem to have two pairs of tables for the same purpose: Labor Billing, Labor Billing Line Items would both seem intended to hold the same data and Material Billing, Material Billing Line Items both seem intended to hold the same data. That's where I need some clarity.

                      Here's a simplified demo file of what I have in mind as a good solution. Check out both the data enter and report layouts. If you have questions about how or why I set things up this way, let me know.