This is a public Forum  publicRSS

Forum post

    will rollo
    Import issue with look ups..
    Forum post posted September 22, 2012 by will rollo 
    59 Views, 2 Comments
    Title:
    Import issue with look ups..
    Post:

    Following on from my post the other day, I have created a rather long script to use when I have updated my database and want to import any new records from the active database, Basically, once I have made a copy of the active database, altered it, tested it, I run this script (see bottom of post). The actual script includes all the tables I have that have records in them. As far as I can see, once it has done the import, it all works - except:

    I have a join table between my products table and my invoice table, This is called invoice items. On my invoice layout, I use a portal to invoice items  and have the usual pk and description, price, quantity fields etc. These are look ups/auto enter calcs (getting data from the products table). Once the user clicks on the PK field, the drop down menu appears using a value list from the products table below.

    Primary Field

     

    The problem I am having is although I can scroll down the drop down menu, once I Select something, all the fields remain empty....

    The other exising invoices have imported fine with all their fields in the portal containing the correct data..

    Any ideas why? I assume it is a look up issue but not sure how to work around it...

    Thank you

    Ps the ref to using the GetNextSerialValue in the prev post I didnt quite get - shoudl I need it if i use this sort of script below?

    • Go to Layout [ “INVOICE ITEMS” (INVOICEITEMS) ]
    • Freeze Window
    • Show All Records
    • Delete All Records [ No dialog ]
    • Import Records [ Source: “filemac:/Macintosh HD/Users/williamrollo/Dropbox/Filemaker/Filemaker 12/Acres Farm.fmp12”; Target: “INVOICEITEMS”; Method: Add; Character Set: “Mac Roman”; Field Mapping: Source field 1 import to INVOICEITEMS::ID_InvoiceLI_pk Source field 2 import to INVOICEITEMS::Item Source field 3 import to INVOICEITEMS::Description Source field 4 import to INVOICEITEMS::Price Source field 5 import to INVOICEITEMS::Quantity Source field 8 import to INVOICEITEMS::Creation_Account Source field 9 import to INVOICEITEMS::Creation_Timestamp Source field 10 import to INVOICEITEMS::Modification_Account Source field 11 import to INVOICEITEMS::Modification_Timestamp Source field 12 import to INVOICEITEMS::ID_Invoice_fk Source field 13 import to INVOICEITEMS::ID_Item_fk Source field 14 import to INVOICEITEMS::Date Source field 15 import to INVOICEITEMS::qbTxnID Source field 19 import to INVOICEITEMS::Long Description Source field 22 import to INVOICEITEMS::Discountable Source field 25 import to INVOICEITEMS::Hidden Source field 26 import to INVOICEITEMS::Category Source field 27 import to INVOICEITEMS::Report ] [ No dialog ]
    • Set Variable [ $$fmauto_1065430; Value:1 ]
    • Go to Layout [ original layout ]

    Answer

     

    • PhilModJunk

      I have a join table between my products table and my invoice table, This is called invoice items.

      This describes three tables, yet your script only imports records into the InvoiceItems table. You need to import records into ALL tables, so there should also be import records steps for Invoice and Products.

      For these imports, make sure that you do not enable the auto-enter option. You want to import all your data exactly as it exists in the other file.

      Ps the ref to using the GetNextSerialValue in the prev post I didnt quite get - shoudl I need it if i use this sort of script below?

      Odds are that you do not need GetNextSerialValue at all. You do need code that uses Set Next Serial Value to update the settings on every single field that has the Serial Numbers auto-enter option specified. GetNextSerialValue is a tool that can be used to determine the value to use with Set Next Serial Value, but it has to be performed in the original file and then the value it calculates must be passed to the new file--this can be complicated and 99 times (or more) out of 100, you need only determine the largest serial number value in the records just imported and use set next serial value to set the serial number value to be at least one larger than that value.

    • will rollo

      Hi phl

      Sorry, I wasnt that clear earlier. The script I wrote to import records does include all the tables, i just didnt want to fill out the post with it all as was repetetive. I had autocorrect off also. But what is odd, is that it has suddenly started working...Very strange as there is nothing worse than 'fixing' something without knowing how!

      Thanks again