9 Replies Latest reply on Jun 10, 2016 2:07 AM by williamrollo

    Create records from a single field

    williamrollo

      I am trying to sort out some final imports from an old access database that is more or less an invoicing solution. My new FM database works in a more conventional system ( using separate Line items tables between products and invoice Table). However, I see in the exported XLS table from the old Access DB that each invoice record is on a single line, with no separate join table for the invoice lines. Each record contains a series of fields that correspond to the relevant lines of the invoice. I have attached a mini version to demonstrate. Is there  a way to script an import from this table to create new records from each of the lines so that record 1 will show the invoice number, line 1 of the product and line 1 price - three fields in total for each record...

       

      My excel file obviously is v different and has many more fields in each record, but if this can be sorted then I should be able to expand to fill out the other fields in the record. (about 800 lines in the xls spread sheet - each invoice has 8 Lines maximum so 6400 records will be created i think)

       

      Many thanks for help and advice.

        • 1. Re: Create records from a single field
          Mike_Mitchell

          Yes, you can certainly do this. What I normally do when presented with something like this is script it. The process looks something like this:

           

          1) Create a table in your FileMaker solution that corresponds to the Excel sheet. (Hint: Easy way to do it is to import the data to a new table.)

           

          2) Create a relationship between the new table and your LineItems table, based on Invoice ID. Allow the relationship to create records on the LineItems side.

           

          3) Create a portal on the layout for the new table to the LineItems table, with the appropriate fields.

           

          4) Create a script that loops over the records in the import table. Such a script might look something like this:

           

          Go to Record / Request / Page [ First ]

          Loop

               Set Variable [ $count ; Value: 1 ]

               Loop

                    Go to Field [ LineItems::Line ]      // This field has to be inside the portal on the layout

                    Go to Portal Row [ Last ]    // This puts the focus in the row where a new record is created

                    Set Field [ LineItems::Line ; Evaluate ( "ImportTable::Line " & $count )

                    Set Field [ LineItems::Price ; Evaluate ( "ImportTable::Price " & $count )

                    Set Variable [ $ count ; Value: $count + 1 ]

                    Exit Loop If [ $count > 4 ]          // Assuming you have a maximum of 4 line items embedded in each record

               End Loop

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

          End Loop

           

          HTH

           

          Mike

          1 of 1 people found this helpful
          • 2. Re: Create records from a single field
            williamrollo

            Dear Mike. That is just the kind of solution that I was hoping for. I had thought a loop script was involved but am not that experienced with looping.
            I shall give it a go tomorrow and let you know how I get on.

            1 question though. The variable $count - is this based on the field that I wish to be its own record? I just can't quite see how the script knows which fields in the record are to be singled out. Should I format the xls file so that the ten fields are in a row. ? 5 line items followed by the corresponding 5 prices? Plus invoice ID. I can see the end loop ends it after 5 fields have been separated.

            Or ami missing something?(likely!) do I run the script again setting the variable for the next field in the sequence?

            Thank you v much

            • 3. Re: Create records from a single field
              Mike_Mitchell

              These two lines:

               

                        Set Field [ LineItems::Line ; Evaluate ( "ImportTable::Line " & $count )

                        Set Field [ LineItems::Price ; Evaluate ( "ImportTable::Price " & $count )

               

              determine which fields are being populated. You will need one Set Field statement for each field you're populating on each LineItem record.

               

              $count will correspond to Price 1, Price 2, Price 3, etc. (for example). The Evaluate function allows you to avoid hard-coding it, but rather just loops over the number of fields present and creates new line items for each one.

              1 of 1 people found this helpful
              • 4. Re: Create records from a single field
                williamrollo

                Great. Thank you for clearing that up. Will test out tomorrow

                • 5. Re: Create records from a single field
                  williamrollo

                  I have created this script but it needs tweaking I think..As it doesn't work - surely due to my misunderstanding - Csan you let me know where I have gone wrong?

                  • Go to Record/Request/Page [ First ]
                  • Loop
                  • Set Variable [ $count; Value:1 ]
                  • Loop
                  • Go to Field [ INVOICEITEMS::ID_Item_fk ] [ Select/perform ]
                  • Go to Portal Row [ Select; Last ]
                  • Set Field [ INVOICEITEMS::ID_Item_fk; Import::type1 & $count ]
                  • Set Field [ INVOICEITEMS::Price; Import::cost1 & $Count ]
                  • Set Field [ INVOICEITEMS::ID_Invoice_fk; Import::ID_Invoice_pk & $count ]
                  • Set Variable [ $Count; Value:$Count +1 ]
                  • Exit Loop If [ $Count >8 ]
                  • End Loop
                  • Go to Record/Request/Page [ Next; Exit after last ]
                  • End Loop
                  Fields used in this script
                  Scripts used in this script
                  Layouts used in this script
                  Tables used in this script
                  Table occurrences used by this script

                   

                  I have a portal on my newly created 'import' layout (new table from importing records). The portal is based on the Invoice Items table (my lines table). In the portal I have the price, Invoice fk, description and item number fields. My import table has many more fields that I don't need.

                   

                  My new import table field ID-Invoice_pk is linked to the Invoice Items field id Invoice fk. Create new records ticked.

                  When I activate the script, then 63 records are created I did reduce the total records in the import table to just 9, to save time testing .So it appears to be created the right amount of records for each of the line items, I guess. Its just that the fields are all empty in the newly created records in the portal (and in the original invoice items table)

                  Any help would be hugely appreciated

                  • 6. Re: Create records from a single field
                    Mike_Mitchell

                    Two mistakes. This:

                     

                    Set Field

                    should be

                     

                    Set Field [ INVOICEITEMS::type; Evaluate ( "Import::type" & $count ]

                     

                    So your target field is wrong. You shouldn't be setting the foreign key equal to a data field. By doing so, you broke the relationship.

                     

                    Also, you need to use Evaluate as shown above. This allows you to make use of the loop and $count.

                    1 of 1 people found this helpful
                    • 7. Re: Create records from a single field
                      williamrollo

                      Sorry about this! Oak I have corrected it ( I think) and now attach a copy..

                      What happens now is that 8 records are created for every Import record. Despite most records having under 6 line fields embedded in them.  The only field that has populated is the Id_Invoice_FK. This is correct as this field links the lines to the original invoice details table and it is through its and this field that the relationship is set up.

                      However the type and price fields have question marks in.

                      My item fk field is actually the product code which autofills from the products table that it is linked to. My understanding was that if I populate this field (with import::Type1/2/3/ etc) then the other fields will autofill...

                       

                      Do I just use one variable in this script despite all the other fields that I want to copy over? I dont see how FM knows which are the target field unless I add a variable for each one, like i do with the set field..

                      Thank you again...

                      • Go to Record/Request/Page [ First ]
                      • Loop
                      • Set Variable [ $count; Value:1 ]
                      • Loop
                      • Go to Field [ INVOICEITEMS::Item ]
                      • Go to Portal Row [ Select; Last ]
                      • Set Field [ INVOICEITEMS::Item; Evaluate ("Import::type1" & $count) ]
                      • Set Field [ INVOICEITEMS::Price; Evaluate ("Import::cost1" & $count) ]
                      • Set Variable [ $Count; Value:$Count +1 ]
                      • Exit Loop If [ $Count >8 ]
                      • End Loop
                      • Go to Record/Request/Page [ Next; Exit after last ]
                      • End Loop
                      1 of 1 people found this helpful
                      • 8. Re: Create records from a single field
                        Mike_Mitchell

                        You're still making the same mistake:

                         

                        • Set Field [ INVOICEITEMS::Item; Evaluate ("Import::type1" & $count) ]

                         

                        doesn't exist. There is no field named "Import::type11". You should be making this:

                         

                        • Set Field [ INVOICEITEMS::Item; Evaluate ("Import::type" & $count) ]

                         

                        The "&" symbol concatenates (joins) the first text string to the second. Think of it as tacking the two together with glue, in the order presented. So you don't need the number of the field; that's taken care of by $count.

                         

                        As to the "how many variables do I need" question, you don't need more variables. You just need one Set Field statement for each field to be populated.

                         

                        If you want to cut off the number of records at the number populated, use an If statement to test and see if all the fields are empty. For example:

                         

                        If [ not IsEmpty ( Evaluate ( "Import::type" & $count ) & Evaluate ( "Import::cost" & $count )) ]

                             {do your Set Field stuff here}

                        End If

                        1 of 1 people found this helpful
                        • 9. Re: Create records from a single field
                          williamrollo

                          Mike- I cant thank you enough. Now I understand how it works and have set my the script properly - It was wonderful going to my orders layout to see all the fields complete!

                          Thank you again for your patience and well explained solution.