Problems merging different fields

[mert]

I've been researching and trying to create a table for my needs but having problems and doubting if this is possible with Airtable.

I'm importing a csv/google sheets everyday with ID, date and amount fields. What I'm trying to achieve is, import another csv, if there is a matching ID, it's date and value fields should be added to their relevant fields instead of overwriting any previous values.

ID1 should have both 09/12 date and 500 amount should be linked, in addition to 10/12 date and 1000 amount getting linked too.

And be able to see an IDs amount on a chosen date and also able to sort for last weeks IDs with the highest amounts.

I'd really appreciate if someone can briefly explain the method I should be using or if this is possible at all. Thanks!

[Xavier_G_Sabate]

Hi Mert, 

If I understood correctly you have two files:

• File 1: Google master ID sheet (basic fields + date + amount)
• File 2: Additional ID related information  (date + amount)
• The relatioship between them should be something as: for One ID you have Many date + amount
  
  

If this is correct I would do the next:

• Create a master ID table (table 1) that is fed with File 1, either using the CSV load extension or manually merging the data. You ONLY insert there the IDs and any other generic record information, no dates, no amounts
• Create a second table (table 2) that will contain the data coming from File 2 (date + amoiunt) and also the date and amounts coming from File 1.
• You must create a field named ID in table 2 that is in fact a linked one to the ID field in Table 1
• Every time you load File 1, you populate the list of unique IDs (you can use the CSV load extension with a merge condition on the ID field)
• Then you load the data from File 1 into Table 2 to have the basic data + amount coming from the google sheet
• And finally you load the data from File 2 into Table 2 to have the additional data + amount information related to the master IDs

Following this procedure you will ensure that all the dates and amounts are related to an ID, and you can track all of them since they will be separated records, no overwrite will be done and everything will be in the right place.

You can also add additional fields to set the source of the data, maybe a single select indicating if a record in Table 2 has been imported from File 1 or File 2, this is a refinement that can help you for tracking and troubleshooting.

Let me know if this explanation is enough for you to move forward, if not I can provide you with more  details. Hopefully you can solve the issue.

Best regards

Xavier GS