Fusion CSV load for a field that is an external lookup

[VynetaRy]

Has anyone been able to load a CSV that has a value that needs to map into an external lookup field?  I want to create an external lookup to populate brands for different companies. It's a great solution when it is individually assigned on a form, but if I need to load data for 100 products and assign a brand to them, will I be able to map to that field?  I really don't want to have a single dropdown with all of the possible brands or hundreds of dropdowns that need to be maintained. I would also prefer the brand not have to be manually updated later when I have the information at load time.  My vision is to have one Fusion scenario that creates and updates the brand lookup data structure as new brands are added. Then we can use that data for lookups to select a brand for products. The only gap is assigning brands in a bulk data load.

[Sven-iX]

For this usecase I'd stay away from External lookups since that field type is simple a "tag" field: It stores the selected string values - not any ID. So you can stuff arbitrary values into that field. For example this is a JobRole lookup using the WF API) that I've set to  random values.

In our implementations we try to break it out into different fields and use display logic:

- company dropdown

- dropdowns for brands from each company

- use fusion to update the dropdowns (and even add a new brands dropdown when adding a new company)

If you have a use case of dozens of companies each with hundreds of brands, then maybe that approach doesn't work. In a recent project we used dependent external lookups. You could try 

Company dropdown (real dropdown field) 

Brand dropdown (external lookup that queries a datastore based on selected company)

In our case we would add the ID to the returned string so we wouldn't need to do name-matching when further using the data (here the brand)

Now - how to update using Fusion:  If the ExtLookup is a single-lookup - use the Update/Create modules.

If you need to put multiple selections into the ExtLookup, the regular modules won't work due to how they pre-process data. They've built in such a way that the field literally needs to be set to an array in JSON format. CustomAPI call is the only option.

Use a transformToJSON module to create the string of the array (that will handle escaping quotes), and in the customAPI JSON, set the field to that JSON string.