1 2 Previous Next 17 Replies Latest reply on Jun 25, 2014 8:54 AM by philmodjunk

    Raising one invoice then duplicating for all active Accounts/Contacts

    EdwardDredge

      Title

      Raising one invoice then duplicating for all active Accounts/Contacts

      Post

           Hi All, 

           I am using FMStarting point which really has been a life saver for me. I have managed to change a few things add a few scripts etc. But I'm struggling to create a batch/bulk invoice script. 

           Overview.

           Running a community based theatre school we have the parents as the accounts and the child as the contact. I need to create one invoice (they all pay the same subs) and have a script that duplicates that across all accounts (parents), taking in to consideration to ensure that the contact (child) is related to the invoice.  and then it emails out with a standard worded email that inserts name fields and invoice number into the email body.

           I've played around for what seems like forever and taken advice but I seem unable to get any script to work. If anyone could help I would be so grateful.

           Kind Regards 

           Eddie Dredge 

            

            

            

            

            

        • 1. Re: Raising one invoice then duplicating for all active Accounts/Contacts
          philmodjunk

               Do you have these relationships?

               Accounts----<Contacts-----<Invoices

               or these?

               Contacts>------Accounts-----<Invoices

               Either way the first part of your script would be to pull up a found set of all students (contacts) to be invoiced. Can you accomplish that part in a script?

               What version of FileMaker are you using?

          • 2. Re: Raising one invoice then duplicating for all active Accounts/Contacts
            EdwardDredge

                 HI Phil, 

                  

                 It seems to be 

                  

                 Contacts ----Accounts -----Invoice

                  

                 I will try a script to pull a found set.

            • 3. Re: Raising one invoice then duplicating for all active Accounts/Contacts
              EdwardDredge

                   Would something like this work to find

                    

                    

              • 4. Re: Raising one invoice then duplicating for all active Accounts/Contacts
                philmodjunk

                     That contradicts what you asked for:

                     

                          I need to create one invoice (they all pay the same subs) and have a script that duplicates that across all accounts (parents), taking in to consideration to ensure that the contact (child) is related to the invoice.

                     by your last answer contacts cannot be related to an invoice, contacts are related to an account and each new invoice will be related to an account.

                     And what version of FileMaker are you using? Some script variations of the answer I might post will only work with the very latest version of Filemaker so I need to know what version you are using.

                • 5. Re: Raising one invoice then duplicating for all active Accounts/Contacts
                  EdwardDredge

                       Hi Phill, 

                        

                       Apologies. I'm using filemaker 13 Pro Advance. 

                       I have uploaded the images for accounts/contact invoice relationship. It may be better than me giving you the wrong answer. Hope they help. 

                  • 6. Re: Raising one invoice then duplicating for all active Accounts/Contacts
                    EdwardDredge
                    /files/3bf3c32865/Screen_Shot_2014-06-23_at_14.27.48.png 1029x478
                    • 7. Re: Raising one invoice then duplicating for all active Accounts/Contacts
                      philmodjunk

                           Interesting. In the design of this database, an invoice is related to both an account and a contact:

                           account----<invoices>----Contacts

                           Do you also have line items on your invoice?

                           The script that you show can be used to pull up a found set of all active students. So it can serve as an initial part of your script.

                           The next part, which only works in FileMaker 13 but saves a lot of switching back and forth between layouts is to add a summary field, sIDList to the contacts table. Define it to produce a list of the ContactID's in your found set.

                           Then you can add an "Exit Script[]" step just before the End IF and add this step to the end of the script that you have written so far:

                           Set Variable [$ContactList ; value: T05_Contacts::sIDList ]

                      • 8. Re: Raising one invoice then duplicating for all active Accounts/Contacts
                        EdwardDredge

                             Hi Phill 

                             We do have line items on invoice. See Screen shot. 

                              

                             The second part of the script has me completely lost. I will have a go at trying to understand. 

                              

                              

                        • 9. Re: Raising one invoice then duplicating for all active Accounts/Contacts
                          EdwardDredge

                               Ok, Think I have managed that , see screen shot. How would I now implement the script to produce the invoices.

                                

                                

                          • 10. Re: Raising one invoice then duplicating for all active Accounts/Contacts
                            philmodjunk

                                 Yes, that should work as a first part of what you need. But it's only a part of the entire process.

                                 Can you upload a screen shot of the portion of the relationship map on which the invoices layout is based? Can you open the table occurrence boxes so that I can see the names of the match fields used? (that will help me be more specific.)

                                 But here's a rough outline of what your script will need to do.

                                 We'll need to also capture a list of Account ID's, one for each contact record. The details for that will depend somewhat on the options made available via the different table occurrence groups of your database.

                                 Then, after creating the first invoice with line items the way that you want it, you'd click a button for a script that saves changes to your contacts layout and performs the steps shown thus far. Then your script can use a loop and GetValue to loop through the values of $ContactList, generating one duplicate of the invoice record for each and also looping yet again to duplicate each line item record.

                                 This last part of the process will adapt the script found here: Duplicating Bill Of Materials (duplicating portal line items) to work with a list of multiple contacts--linking each new invoice to a different contact and it's related Account.

                            • 11. Re: Raising one invoice then duplicating for all active Accounts/Contacts
                              EdwardDredge

                                   Hi Phil,

                                   That's great thank you so much. I'm just collecting my son so not in front of the computer at the moment,  but I will send details as soon as I'm home. 

                                   Really appreciate all your help. 

                              • 12. Re: Raising one invoice then duplicating for all active Accounts/Contacts
                                EdwardDredge

                                     HI Phil, 

                                      

                                     I have attached a link to a PDF of the relationship tables. Please let me know this is all Ok to work with. The invoice table is far right. 

                                https://www.dropbox.com/s/r3hgb8cn2m8swmc/Relationship%20Graph.pdf

                                • 13. Re: Raising one invoice then duplicating for all active Accounts/Contacts
                                  philmodjunk

                                       While you expanded the TO's for the "buoys" in this Anchor Buoy organized set of Table Occurrences, you didn't expand the T12_Invoices table occurrence. I will assume that the primary key field in that table is named: ID_Invoice as that seems consistent with the naming conventions used in this solution.

                                       First, we need a matching list of Account ID's to go with our list of Contact IDs.

                                       Define a calculation field, cAccountID as:

                                       T05a_contacts_ACCOUNTS||id_account|::id_account

                                       Be sure to select T05_contacts in the "context" drop down in the top of the specify calculation dialog.

                                       Then define sAcctIDList as a summary field that returns the list of cAccountID.

                                       Then we can put your script together like this:

                                  #Start this script from a layout based on the T12_Invoices TO. Create the needed invoice, then run this script:
                                       Go to Layout ["Contacts" (T05_Contacts) ]

                                  #Put your find script from 6/23 here

                                       Set Variable [$AcctIDList ; value: sAcctIDList ]
                                       Go to Layout [original layout]
                                       Freeze Window
                                       Loop
                                          Set Variable [$K ; value: $K + 1 ]
                                          Exit Loop IF [ $K > ValueCount ( $AcctIDList ) ]
                                          Set variable [$RecordNumber ; Value: Get ( RecordNumber ) ]
                                          Duplicate Record
                                          Set Field [T12_INVOICES::id_contact ; GetValue ( $ContactList ; $K ) ]
                                          Set Field [T12_INVOICES::id_account ; GetValue ( $AcctIDList ; $K ) ]
                                          Set Variable [ $InvID ; value: T12_Invoices::id_Invoice ]
                                          Go To Record/Request/Page [$RecordNumber]
                                          If [ Not IsEmpty ( T12j_invoices_LINE_ITEM||id_invoice|::id_invoice ) // there are line items to duplicate ]
                                             Go To Related Record [Show only related records; From table: T12j_invoices_LINE_ITEM||id_invoice|; Using layout: "lineItems"
                                                   (T12j_invoices_LINE_ITEM||id_invoice|)]
                                             Go To Record/Request/Page [first]
                                             Loop
                                                Duplicate Record
                                                Set Field [ T12j_invoices_LINE_ITEM||id_invoice|::id_invoice ; $InvID ]
                                                Omit Record
                                                Go To Record/Request/Page [first]
                                                Omit Record
                                                Exit loop If [ get ( FoundCount ) = 0 ]
                                             End Loop
                                             Go To Layout [original layout]
                                             Go To Record/Request/Page [Last]
                                          End IF

                                       End Loop

                                       This is a complex script and I could easily have included either errors or references to layouts or fields that do not exist in your database so you will need to examine it and test it carefully. I recommend that you temporarily remove or disable Set Error Capture[on] and Allow User Abort [off] while testing and that you test this on a copy of your database so that you can revert to your original set of records if this fails.

                                       If you have FileMaker Advanced, I'd use the script debugger to step through this script during my first test of it.

                                  • 14. Re: Raising one invoice then duplicating for all active Accounts/Contacts
                                    EdwardDredge

                                         Hi Phil, 

                                          

                                         Thank you so much for doing this. I have implemented the script but it seems to only duplicate one invoice and get stuck on

                                       Go To Record/Request/Page [$RecordNumber]

                                    and asks me to specify number

                                    Please see screen shot. I have also added the script in full as I have implemented it.

                                    1 2 Previous Next