Sign in to follow this  
Followers 0

importing from excel; match field required for new record

2 posts in this topic

I'm setting up an excel file for import to "update matching records in found set" records and selected the option to "add remaining data as new records." My match field is the sys_id. What I"m finding is filemaker won't add the remaining data as new records unless there's a value in the sys_id match field. Normally, that field is set to auto-enter a serial number in sequence. I would prefer not to create a sys_id value in the excel in case someone has created the same one from within filemaker.

Any reason Filemaker is requiring data in the match field to create a new record with the remaining data?

Thanks for any help and advice.



Share this post

Link to post
Share on other sites

If I understand this correctly, you should create a calculation field = sys_id and use it as the match field. Then your new records can have any arbitrary value in their sys_id cell. Make sure to apply auto-enters during the import.

I am assuming here that the existing records do have a sys_id value and that it is the correct one - otherwise your question makes no sense.


Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  
Followers 0

  • Similar Content

    • Container Fields and Printing
      By Answers
      In our database solution we have a table that holds other documents relating to the data. The enduser might insert a PDF or a JPG. So the table is made up of a container field, a job number (unique identifier). To view the file the user clicks in the container (script exports field contents and automatically opens).
      The thing we would like to do is when printing a job report if there are any "external documents", would like them to print as well. We want it be be part of the complete report. The container is setup for interactive content and start playback automatically. The script exports the container and imports it back in... on the Mac it produces the correct results, on the PC  it prints as a pdf icon for example. Maybe what I want is not possible. Any suggestions.
    • FileMaker 14 as data source to MS Access
      By mr_mike
      I have a client that would like to use MS Access to connect to a table in a solution hosted on FMS 14.0.3.  What I'm reading suggests that was possible with .fmp5 and .fmp7 but is no longer available in .fm12.  I've tried to use Access and both (mac) Excel and (windows) Excel to connect via ODBC to the FMS hosted table and I can construct the query and see the fields, but when I submit the request I get an error.  Is it still possible to connect Excel or Access to FileMaker 14.0.3 (hosted)?  The error I get appears on the attached screenshot.
      Is anyone currently doing this?  
      Thank you in advance.

    • XML Import: Transforming XML Data with XSLT
      By Paul Webb
      I've spent the last couple of days learning XML, XPath, XSLT trying to make this work. I feel like I am really close but something simple is off. My XSLT is not quiet right and I need some expert input. For testing I set it up to only pull in the first field. Once I have that right the rest should be easy.
      Below I have added the XML, XSL, and Transformed XML. You can see in the transformer portion that there is something off with my data. When trying to import into FM it just creates a blank record. I am trying to import the "Y" from the "BTK-security-complete" field.
       <?xml version="1.0" encoding="UTF-8" standalone="yes"?>   <CDETS xmlns="cdetsng" xmlns:ns2="">       <Defect id="CSCsa8849" ns2:href="">           <Field name="BTK-security-complete">Y</Field>           <Field name="Class">CSC.swtools</Field>           <Field name="Component">other</Field>           <Field name="DE-manager">bnado</Field>           <Field name="Description">Lifecycle changes to Pre when a Obsolete concept moved under Pre Parent</Field>           <Field name="Headline">Lifecycle changes to Pre when a Obsolete concept moved under Pre Parent</Field>           <Field name="Identifier" ns2:href="">CSCsa8849</Field>           <Field name="Is-customer-visible">N</Field>          <AuditTrail id="Tue Apr 19 12:27:53 PDT 2005" defectID="CSCsa8849" ns2:href="">               <Parent ns2:href="">CSCsa8849</Parent>               <Field name="ChangedBy">ksahani</Field>               <Field name="ChangedOn">04/19/2005 12:27:53</Field>               <Field name="Field">Defect Created</Field>               <Field name="Operation">New Record</Field>           </AuditTrail>           <Files>               <File id="DDTS_History.txt" defectID="CSCsa8849" ns2:href="">                   <Parent ns2:href="">CSCsa8849</Parent>                   <Field name="Extension">txt</Field>                   <Field name="FileSize">55</Field>                   <Field name="Filename">DDTS_History</Field>                   <Field name="UpdatedBy">cdetsync</Field>                   <Field name="UpdatedOn">01/22/2008 17:25:00</Field>               </File>           </Files>       </Defect>   </CDETS>    
       <?xml version="1.0" encoding="UTF-8"?>         <xsl:stylesheet       version="1.0"       xmlns:xsl=""       xmlns:fmp=""       exclude-result-prefixes="xsl fmp">           <xsl:output         method="xml"         version="1.0"         encoding="UTF-8"         indent="yes">     </xsl:output>           <xsl:template match="/">              <FMPXMLRESULT xmlns="">         <ERRORCODE>0</ERRORCODE>         <PRODUCT BUILD="10-18-2015" NAME="FileMaker" VERSION="ProAdvanced 14"/>         <DATABASE             DATEFORMAT="m.d.yyyy"             LAYOUT=""             NAME=""             RECORDS="{count(/*/*)}"             TIMEFORMAT="k:mm:ss "/>         <METADATA>           <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="BTK-security-complete" TYPE="TEXT"/>         </METADATA>         <RESULTSET FOUND="">                 <xsl:for-each select="/*/*">             <ROW MODID="0" xmlns="">               <xsl:attribute name="RECORDID">                 <xsl:value-of select="position()" />               </xsl:attribute>               <COL>                 <DATA>                   <xsl:value-of select="//Field[@name='BTK-security-complete']" />                 </DATA>               </COL>             </ROW>           </xsl:for-each>               </RESULTSET>       </FMPXMLRESULT>     </xsl:template>   </xsl:stylesheet>    
      Transformed XML
       <?xml version="1.0" encoding="UTF-8"?>   <FMPXMLRESULT xmlns="">      <ERRORCODE>0</ERRORCODE>      <PRODUCT VERSION="ProAdvanced 14" NAME="FileMaker" BUILD="10-18-2015" />      <DATABASE TIMEFORMAT="h:mm:ss " RECORDS="1" NAME="" LAYOUT="" DATEFORMAT="m.d.yyyy" />      <METADATA>         <FIELD TYPE="TEXT" NAME="BTK-security-complete" MAXREPEAT="1" EMPTYOK="YES" />      </METADATA>      <RESULTSET FOUND="">         <ROW MODID="0" RECORDID="1">            <COL>               <DATA />            </COL>         </ROW>      </RESULTSET>   </FMPXMLRESULT>  
    • Audit Log while importing records
      By tetrasync
      I'm looking for help with creating a script to transfer amended records into an audit log. For context I currently have a separate table that any amended fields move over to, how ever this works for one field at a time, but I'm running into problems when bulk importing a bunch of new records. When I import new records I get them to match up on one common field, the rest I want to record in the audit log but only if theres been a change. So far my script looks like this:

      This is just for one field and there are about ten I wish to audit log if theres been a change, naturally I can just repeat this part of the script and adjust accordingly. My problem is getting it to loop for every record. Obviously I want to set the variable for the old record for every and compare it before moving onto the next, but Im just unsure how to structure it and where to put my loops as if I loop the whole thing and use move to next record, it'll ask me to import new records each time it gets to that part of the script.
      So is there anyway to create a loop but get it to ignore the import records after the first loop? Or do I need to approach this differently?
      Kind Regards.
    • Importing Between Tables within File on Server
      By Chuck
      I have a script that works correctly on a server machine when running within FileMaker Pro 13, but raises errors when run within FileMaker Server 13. Both are running under Windows. The portion that is raising the error is an `Import Record` script step that imports from one table within a file into another table within the same file.
      The error returned is 100, "File is missing," so I'm wondering if this is something not supported when running a script within FileMaker Server. If that's the case, I'm thinking that perhaps exporting the records to a temporary file and importing from that might be a workaround, but before I start down that road, I want to check and see if I'm missing something.