5 Replies Latest reply on Jun 23, 2012 1:58 PM by MikeF_1

    Import Expense Template



      Import Expense Template



      Have a layout based on table Events.

      There is a portal to table EventExpenses on that layout.


      The objective is to click a button that will automatically
      import records from table ExpenseBlankTemplate into that portal, after a new
      blank record is created in the layout.  
      Note – this would be a separate function from Add New Record.


      The two tables are related by field EventID.

      When a new record is created in the layout, a new EventID
      [serial number] is generated in table Events.

      If I was to manually add a record into the Expenses portal,
      it would automatically put the relevant EventID from table Events into the newly added record in
      the Expenses table.

      Need that to occur for each of the 30 records from
      ExpenseBlankTemplate that will be imported into the portal [table
      Expenses].  … The EventID’s in the template
      records are blank.


      Also, there is a CompanyID field in table Expenses which
      “looks up” to CompanyID in table Events.

      It also needs to be populated with the relevant CompanyID
      from table Events.

      All CompanyID records in the template table are blank.

      Presume this would take care of itself by checking the
      “Perform Auto-Enter Options”.


      Hopefully the foregoing is clear and concise.


      Any assistance will be greatly appreciated.





        • 1. Re: Import Expense Template

          You can't import records in to the portal--you import them into the table on which the portal is based. After import, you can use Replace Field contents to update specific fields such as EventID and CompanyID in "batch" updates of the records you just imported.

          Here's a general outline:

          Use set variable to save EventID and CompanyID in $Variables
          Import the records from the template table into the expenses table
          use replace field contents with the calculation option--puting the variable for EventID into the calculation to assign an eventID
          use replace field contents with the variable for companyID to assign the companyID--looked up value fields will automatically update when you do this.

          • 2. Re: Import Expense Template


            Had to make some adjustments, but ended up getting it working properly.

            Did the two Set Variables first.

            Then Import Records.

            Then "Go To Layout -- Expenses" table so FM would isolate the Found Records.  [Without that, it was replacing EVERY record in Expenses with those variables.  Good thing I worked on a backup !!]

            *Then* Replace Field Contents with the variables.

            Then back to Original Layout.

            At any rate, as usual you certainly pointed me in the right direction.



            • 3. Re: Import Expense Template


              Have found an anomaly I can't seem to fix, hopefully there's an easy answer.

              Re the Import Records portion of the script -- it asks for a file name to import the Expense template from.  I had it as the backup/test file name that was being used.

              Now that the script being implemented in the "master" file, it still asks for the name of that backup file.

              In Import Records "Specify File" dialog box, is there a way to state "THIS FILE" -- whatever it happens be ???




              • 4. Re: Import Expense Template

                You can just choose add file and then add the file itself.

                But the problem is that when you change the filename (which shouldn't really happen normally) your import breaks.

                If you want to prevent this from happening you can set the current filename as a variable and use that variable as the filename to import from.

                Two script steps in your import step (you should already have the import step, so only add a "Set Variable"):

                Set Variable: [$Filename ; "file:" & Get (FileName ) & ".fmp12"]
                Import records    -   Set your filename to be $Filename

                Note that this is for Filemaker 12. For filemaker 11 replace fmp12 with fp7

                Script steps

                • 5. Re: Import Expense Template


                  That should work, it's exactly what I was looking for.