Import into table with Multiple Choice (Dynamic) field/column
I have the MC(D) working, but need to import a lot of records into the table. They currently exist in a spreadsheet with that column already filled with the correct data, but how does one have to configure the list of choices for the MC(D) field in that column of the import file?
I'm using tab delimited for the import file as that works and avoids mesing with quoting text fields and I've tried importing with the MC(D) fields set to the text that is normally returned from the linked table, separated by commas and also by semi-colons. I then replaced the text with the Id values that identify the appropriate records, again separated by commas and then by semi-colons. But nothing works correctly. The import completes successfully with no error, but the MC(D) column seems to contain a random selection of links, certainly the import has not been able to link the correct records in the table that holds the Dynamic choices.
I really don't want to have to enter this data all by hand. How are we supposed to import data so that MC(D) columns are correctly linked?
Any ideas/help here?
Just an idea — I did *not* test this. The blog entry announcing version 3.4.0, which seems to have disappeared, mentioned that a MC(D) field can be set by using the "array" notation — actually an array of numbers enumerating the ids of the records in the source table:
MCD := [ id , id, id, … ]
It did not tell if this would work as an import syntax, but I would try, just in case. Please let us know the result if you try this.
Alain, yes, it does work and here is a link to that blog post https://ninox.com/en/blog/ninox-version-update-340 . Thanks for the work that was done here https://ninox.com/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/dynamic-multiple-choice-field-how-to-programmatically-assign-6054811e78d52c6ec49c671e , however, the assignment of MCD values is now inline with how the static MC fields are assigned programmatically.
I doubt you can import the assignment directly. You will most likely need to iterate through the imported table with a for-loop and assign the Id's
I had to check this… Unfortunately, the array notation is not interpreted correctly when importing data fron an external file. What is more surprising is that "Update multiple records…" does not update the MCD fields correctly even if one enters a formula returning an array. This seems to work:
- create a text field to hold the list of comma separated ids you already produced (InMCD);
- create a button with the script:
for r in select myTable do
let a := split(r.InMCD, ",");
r.('Multiple choice (dynamic)' := for n in a do
- while importing the records, import the comma separated list of ids in field InMCDN
- touch the button.
UKenGB, if "How are we supposed to import data so that MC(D) columns are correctly linked?" means the same as correctly selected, then the following code will iterate through the table and assign the selected values...
let selectedId := 1;
for i in select TableContainingMCD do
let selectedIds := split(text((select MCDTableWithSelectedValues where Id = selectedId).Ids), ",");
let aryIds := for j in range(0, count(selectedIds)) do
i.(MCD := aryIds);
selectedId := selectedId + 1
The code assumes that the Id numbers in TableContainingMCD match the Id numbers in MCDTableWithSelectedValues, otherwise, you will need to make adjustments.
Alain, I guess I need to refresh my browser before I post. 😉
I had made a table of field types and the required input, import and number and text conversion results. I did a screenshot capture, but it didn't come out readable, nor did the actual table it was in, as it converted it to text.
The jist of my findings was that to import choice field data, you have use their item names with multi-choice as a comma-separted list of names.
Other fields that may not be as obvious:
Location: name<lat, long>, lat and long are decimal numbers
Color: #<six-digit hex>, without the angle brackets
Icon: its name
Thanks for the ideas, but in fact I ended up just setting the MC(D) field manually after import. It was going to take me longer to figure it out than just doing it manually.
Good information for future imports though.