Bulk update of a one-to-many record relationship

Knack Community
1

Hi,

I have a Date data table connected to a number of available Fleet Cars data table. Each date is connected to more than one car. As a car becomes unavailable I need to update the Date table by removing just that one car from a specific date, while leaving the rest of the cars connected to the Date (as they are still available).

I have tried preparing a csv file, with the columns of Date, Car and Availability. In the Car column, I have all the Cars that should still be connected to the Date. I then import that to the Builder, to try and update the Car-connection field. But that doesn’t work - the import then just wipes the Car field (i.e. whatever connected cars were linked in that field are deleted).

Does anyone have any idea how I can bulk update connected Cars - doing it one by one is not ideal (we have about 20 000 fleet cars on different sites, each of them using it’s own booking engine, etc - so lots of different calendars that I need to combine).

Thanks,
Chrislee

2

Hi Chrislee,

If the car field is being wiped instead of updated, it sounds like a formatting issue or configuration setting. Does your CSV file contain a unique identifier for each car (like an ID)? When importing the CSV, is the Builder configured to use this field?

Also are multiple connection fields separated by a comma without spaces?

Good: Tesla,Ford
Bad: Tesla, Ford

3

Thank you!

The Date field (which is what I use to identify the record that needs to be updated when I import the CSV) is not available to select as an option (below is a screenshot of what I see) - so, to overcome that, I’m just importing the CSV, which then shows the many-connection field that I want.

knack-import
knack-import921×463 35.5 KB

Once that’s done, I manually delete the “old” Date records - that’s the best solution I could find.