11 Replies Latest reply on Sep 7, 2012 2:54 PM by landry

    Trouble automating invoice creation

    landry

      Title

      Trouble automating invoice creation

      Post

      I am upgrading a Filemaker Pro 5.5 instalation to FMP12. In addition to upgrading the databases, I am combining related databases into one single database with multiple tables. I've run into a problem with the advertising database: the process used to generate invoices under FMP5.5 includes a step in which the found set of customers (those being invoiced this month), is exported from one table and imported into another.

      Here's how the step works:

      • Find all records for month being invoiced
      • Export Customer ID and Ad Serial (both unique IDs) from found set in Advertising table
      • Import Customer ID into Invoice table and manually enter Ad Serial as needed

      I don't understand why this step is manual but I think it's a bad idea. It allows too many oportunities for mistakes. I would like to automate those steps but am not sure how. My first thought is to use a script that would export the found set of Customer ID and Ad Serial into the invoice table but I don't understand enough about what's going on to know if that's a good idea or not.

      Can anyone sugest a way to automate these steps? Does this even make sense? I'm pretty good with Filemaker, but this bumps into the edge of my knowledge.

      thanks
      LB

        • 1. Re: Trouble automating invoice creation
          philmodjunk

          There is no need to export the data. Import Records can import a found set of records from one table to another table. (There wasn't in 5.5 either BTW.) The two tables can be in different files or the same file and the same steps are used to import the data.

          This can be scripted so that the user does not need to worry about mapping the fields of one table to those of another as part of the import--these settings can be specified in the Import Records script step.

          • 2. Re: Trouble automating invoice creation
            landry

            Thanks. I didn't think that the manual export/import was a necessary step. I'll look into Import Records and see if I can figure the best way to do what I need to do.

             

            • 3. Re: Trouble automating invoice creation
              landry

              @PhilModJunk: I'm looking at this again and don't see a way to import from a found set to a table. Am confused. It seems as though I can import a file and "update existing records in a found set" or "update matching records in a file set" but I don't want to do either of those. I have two tables in the same database, one containing the found set that I want to import into the other table.

              My primary recources are the FM12 documentation and a copy of FM11: the Missing Manual. Can you point me to other resources that may be helpful?

              thanks

               

              PS. Attached photo may be of help.

              A. 19 items in the found set
              B. UID for advertiser, imports to D
              C. UID for ad, imports to E

              The left and right sides of the image illustrate two tables in the same db. Current process is to export A,B from the left table (Advertising) and then import A into the right table (Invoices). E is entered manually and retrieves the line item associated with that UID.

              • 4. Re: Trouble automating invoice creation
                philmodjunk

                Manual steps to import a found set from table A (the source table) to Table B (the target table):

                Go to a layout for Table A and perform a find to produce the found set that you want to import.

                Go to a layout for Table B. (Not a layout with a portal to table B, you want a layout that refers to table B in layout setup...)

                Select Import Records | File from the File menu.

                An open file dialog will appear, select the very same file you already have open. Select Table A from the list of tables that next appears.

                Map fields as necessary so that the data will be imported in the right fields of Table B.

                In the Import Action section of this dialog, select the Add Records radio button.

                Then Click Import, then click it again in the last small dialog that appears to import the records from the found set in Table A to the found set in table B.

                Once you can do this manually and get it all to work, you can try creating a script for this as it uses the same dialogs for the most part to set up the Import Records script step. The main difference is that you don't have to go to a layout based on the Target table for this to work from a script.

                • 5. Re: Trouble automating invoice creation
                  landry

                  Ahh, I see now. That works well but has highlighted another issue: Table B includes a portal to a third table. When I import Table A, it retrieves all the Ad Serials for that Customer ID. I'm thinking that I can filter that, however. Will poke around and see what I come up with.

                  Thanks again!

                  • 6. Re: Trouble automating invoice creation
                    philmodjunk

                    Your import is not importing ANY data from the portal.

                    You'll need to look at the portal relationship. At best guess, you are linking to portal records by the customer number field but I could easily be wrong. When you import the records, they have the same match field value as the imported records (the same customer number value if I have guessed correctly). They thus match to exactly the same portal records as the original. If this should not be the case, the match field of the records you just created with this import should be modified to no longer have the same values as the originals from which they were copied by the import.

                    If the match field is an auto-entered serial number intended to uniquely identify each record in this table, you should enable auto-enter options during import (It's a check box in that last little dialog before the import starts) so that this field generates a new, unqiue serial number for each record created by the import.

                    In other cases you may just need to clear this field for each record created by the import. A single replace fields step can do that if such is necessary.

                    • 7. Re: Trouble automating invoice creation
                      landry

                      I understand part of what you're saying but not sure I'm getting it all. I really appreciate your patience and help here.

                      Posted an image below of the target layout. The green items are from, Table B and the rest from Table A. When I import the Customer ID from A, it displays with it related records in the portal. This is not the way it works when I did it manually in FM5. In that case, Tables A and B were in separate databases and I had to manually enter the Ad serial into the portal (referencing Table A) after I'd imported the Customer ID from Table A. 

                      Just to be safe, I went back to a version of the db before today's tests. I ran a Find and imported the found set as instructed above (with auto-enter options enabled). I got the results on the right of the image below: all the Ad Serial for that customer # are showing up, but I only want one Ad Serial and I haven't entered that yet so the portal should be blank.

                      I get the feeling that FM12 is doing exactly what it's supposed to and that the problem is my error. So … I'm afraid that I'm lost. It sounds like you're saying that something needs to change in the portal, is that the case?

                      FWIW, I've also tried linking the Advertising db to the FM12 Invoice Starter Solution but that created a different set of issues.

                      • 8. Re: Trouble automating invoice creation
                        philmodjunk

                        If you clear the invoice number field, do the records in the portal disappear?

                        If so, check and see if this field is defined as an auto-entered serial number in Manage | Database | Fields.

                        I think that all you need to do is select the option circled in red when you import your records:

                        • 9. Re: Trouble automating invoice creation
                          landry

                          That field is "Indexed, auto-enter serial, can't modify auto." I set it to allow modification and reimported with the Auto-enter option checked. Same results. Deleting the invoice number does not change the records in the portal.

                          • 10. Re: Trouble automating invoice creation
                            philmodjunk

                            Then it's not the field used to link your invoice to those line item records.

                            You'll need to open up Manage | database | Relationships and look at the match fields to figure out what field in your table is used to link to the portal table's records.

                            That field needs to either be cleared or modified in your newly imported records.

                            BTW, you didn't need to remove the "can't modify" option. I DO think you need the "perform auto-enter options while importing" box selected during import--even if only to get a unique number into the invoice number field.

                            • 11. Re: Trouble automating invoice creation
                              landry

                              Thanks. Off to a meeting. Will get back to this later and take a closer look at those relationships.