I am making a table of companies which then list the investors in that company as a multiple select field - like ABC Company as the record name, and then “DEF Fund,” “GHI Investor” as the selected investors. I want to then import a new list of companies and investors, which would only add new ones to this list - not overwrite the existing fields - in this case, lists of investors are often incomplete so ideally I could import multiple lists of investors and companies and capture all the investor names.
So for example, if I imported a list of investors and companies to the base above, which showed that that “DEF Fund” and “JKL Fund” were investors in ABC Company, I’d like the result to be all three - “DEF Fund,” “GHI Fund,” “JKL Fund”. It just adds the new ones, doesn’t remove GHI Fund because it wasn’t in the new list - only additive.
Any thoughts on how to do this? Thanks in advance for any ideas!
Welcome to the community, @Mike_Jackson! :grinning_face_with_big_eyes: A straight import from a CSV file won’t get you what you want. My suggestion is to import the CSV data into a temp table, then run a script in the Scripting block to process it and put the date into your primary tables.
I would also suggest changing the design of your base slightly. Instead of using a multiple-select field for the investors, add an [Investors] table, and link each investor to one or more records in a [Companies] table. That will allow you to track more info about each investor besides just their name, and also make it easier to link new investors to companies without messing up existing links.
With that setup, the script in the Scripting block would parse the records you’ve imported into in the temp table, add any necessary investors and/or companies to their respective tables, and create the appropriate links along the way. Such a script wouldn’t necessarily be simple to create, but it would give you a much better result than a straight import ever would.
If you’d like help creating such a script, post a new thread in the #developers:work-offered category, and include a link to this one so you don’t have to retype so much of your background info.