1 Reply Latest reply on Apr 6, 2017 7:26 PM by CarlSchwarz

    CSV/XLS from JIRA -> update many tables

      Hi I'm pretty new to FileMaker and am struggling to find the best way to do this. I have several tables that need updating via data from a csv/xls that gets exported from JIRA (what we use for our project management and sales tracking solution). It takes the form of the below:

       

      personquantityproductclientorder date
      131A12/01/2017
      1123A17/01/2017
      195B09/02/2017
      2153A03/03/2017

       

      I have created separate person, product and client tables and I want the csv to add records if a new value exists in the csv. So if the next csv was listing a client C that doesn't exist in the client table, it would add a client C. Same behaviour for person and product. Not sure how to de-duplicate the entries, or stop it creating a record if it matches an existing one.

       

      Each line in the csv represents an order, and my goal is to use FileMaker for management to manage the KPI's of the sales team (person) by looking at and storing notes on sale volume, generating some statistics automatically. The reason I thought filmmaker would be great for this is that I could set up an automatic script to run twice daily, so management would have a dashboard that was relatively up-to-date.

       

      Wondering if this is possible, so far I have created a script that imports the csv using the right fields into one table, but not sure where to go from there.

        • 1. Re: CSV/XLS from JIRA -> update many tables
          CarlSchwarz

          gordi wrote:

           

          Hi I'm pretty new to FileMaker and am struggling to find the best way to do this. I have several tables that need updating via data from a csv/xls that gets exported from JIRA (what we use for our project management and sales tracking solution). It takes the form of the below:

           

          person
          quantity
          product
          client
          order date
          131A12/01/2017
          1123A17/01/2017
          195B09/02/2017
          2153A03/03/2017

           

          Each line in the csv represents an order

           

          So if the next csv was listing a client C that doesn't exist in the client table, it would add a client C. Same behaviour for person and product.

           

          Not sure how to de-duplicate the entries, or stop it creating a record if it matches an existing one.

           

          You need to import the orders in the CSV first and then manipulate the data via a script after that.

           

          E.g.

          Step 1) import the csv into the orders table then

          Step 2) Loop through all the newly imported records and

               - Use your own logic to delete duplicates

               - Test if the client, person, or product already exists and if not then add that client, person or product to the relevant tables