Fields not showing inside List in an n:m relationship

rfernandez

Hi,
How are you?
I have these 3 relations:

And this form:

Why the only available field is the id of the record? I would expect the other fields of the table to show.
No apparent need to join with other relations as the fields are alreay there .
This form is just to show the association between the records of the two tables in the n:m relation, I don't need any other fields from indicator or sources relations

Of course if I add the related tables I get the fields.

However I don't need indicator.id, what I need is indicator_sources.id_indicator and the same for sources.

On the other hand if instead of using a list, I use the binding fields, (the n:m relation fields) the fields I need are shown.
The final purpose of all these is to be able to use the CSV import, only available in the list element, to load a lot of data from a CSV in to the n:m table/relation.

Thank you!
Warm regards

lsw-fabian

Hi, what you want is 1) indicator.id

rfernandez

Hi Fabian,
Thank you very much for your answer!

Well, I'd already tried that, but what happens is that I think that the CSV expects to load in the main relation fields, and the indicator.id does not belong to indicator_sources relation but to the indicator relation.

And so, when you hit the CSV button nothing loads.
It should load this:

And instead loads this:

Just in case, I've also checked that the n:m is enabled to show the corresponding fields.

lsw-fabian

I guess you dont have imported the data correctly. Do you find your data in the builder (Relations/Relation/Data view)?
You should also make sure, that your n:m relation to both sides are required.
When importing CSV's you need to define record lookups (all required information can be found on the help page)

rfernandez

Hi Fabian,
I hope you are doing well!

Thank you very much for your answer!! I really appreciate the time you took to explain this.
As a side note I'l invite you to read the Builder documentation and find the place where you can see what you have just explained in such a clear and straightforward way. (spoiler alert: you won't find it). Instead, you will find a short theoretical and generic explanation on CSV inside r3, which will probably be very hard to decode for a regular person. Topic for another conversation ... ;-)

Your instructions work perfectly for single, simple tables. Great!!

Now for my use case: things get a bit more complicated. (n:m relation table)
Assumption: default indexes created by REI3 (unique for record id, non unique for each of the other fields)

First execution loads the records correctly. Me Happy!!
Second execution no changes in the CSV file, loads the same records again duplicating them instead of preventing from loading duplicate records (insert enabled). Me Confused??
Me Thinking ....
Change a few records in the CSV, one new record and one update. The new record is created, new duplication of all the existing records, update ignored.
The index for the record id is unique. However as the record id is generated automatically each time, it will never hit duplicate record constraint, so the unique index is not fulfilling its purpose. Problem!!
Now I create a new index including the 2 fields (excluding the primary key) so each value pair combination is unique. Each indicator can have many sources and vice versa, but no 2 records can be equal.

Sorry its in Spanish :-)

I truncate the table
Update one value for the source column in the CSV
Rerun the CSV upload
Result: error, because of duplicated keys, as of course all the records already exists.

Conclusion: this seems like a deadlock situation in which I cannot insert and update records in the same operation.
If I want to update records I need to create a new form with the insert disabled in the data source, that way it will not check uniquenes for existing records and only lookup the modified record and update it.
But, guess what ... it does not happen because as I updated the value of the record in the CSV, it won't find it in the lookup. in the relation. So no update happens.

My guess is that the source problem is that the record ID, internally managed by REI3 is automatically generated on each CSV import, so the records will always be new when contrasted against the index because the record id will be different and could never be uniquely identified on a second CSV load, as you don't import the record ID.
I checked that directly in the database and it is so.

I'm assuming my rationale is missing some big concept or lacking to understand the r3 flow correctly (or maybe hours after trying to solve the issue I'm so confused that I'm not seeing the obvious :-)) and I'd really love to have your thoughts on this, that I hope allows me to understand how to handle this feature in its full potential, and understand the current limitations.

Maybe what I'm trying to do is not possible, and if so I'd like to now.

Thank you very much!
Best regards
Ricardo