Relationship Issues With CSV Sync

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have a connected table getting sale data from a CSV file in the Google drive.  I must have a unique ID in this table, therefore I added one as Sale ID.  I do have a store number but  as this is linear data by department my store number cannot be unique. 

In order for me to report this sales data together with other Store info,  I need to connect this table to stores table.   

Now the issue is that my stores table has a lot of relationships and in all relationships, I am using record ID # as the key field. This is appropriate as I may not always have a store number until a project is established in SAP. 

Now when I am trying to link my stores table to Sale data, I cannot do so as record ID # in stores table is not relevant to store number in the sales table.  Unless there was an option to have more than one key field, I cannot seem to find the way to link to the connected table.  Any insights? 


   

Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
I have not tested this but it may work.

You can make a Synch table of your stores table for all stores which have a store number assigned.  Use that as a filter to be sure that the will be no stores attempted to Synch unless they have a store number.  In that Synch include a field called [Record ID#] mirror which is a formula field equal to the [Record ID#] to be sure that you know the record idnof the store.

Then on the Stores mirror table, set the key field to be the Store number.

Then use a relationship to your sales data from the stores mirror table and lookup up the record I'd of the store.

Howeve, if that works there will be another obstacle in that you will not be able to make a summary field on that relationship as the reference field on the right is based in a lookup field.  

If you need summary fields, then you would need a process to copy the lookup field into a numeric field and base the relationship on that pure numeric field.  That process could be clicking a button to run API_RunImport to update that field.  

It's also possible that this can be done using a Webhook. Since once the stores table is populated, there will be very few addition to stores in an one day.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Many thanks. I initially created a separate app and had a connected table to Stores and sales data. I had to resort to the similar solution suggested by you but in this case I made the location number as the key field.  But as there were a number of things that were not working in this arrangement (for example, I could not show how many projects have been completed for each store), I decided to bring CSV sync into my main app but then the issue manifested. I may not be so much concerned about the summary fields than the need to show projects completed in each store.  Not too sure if I can link the new sync table to projects. Will check and let you know.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Mark, it works with the projects table when I established the relationship.  So this is a great solution.  Thanks.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
OK, thx for letting me know.  It was a theory but I never tested it.