Validation: The cardinality of the look-up is invalid
Hi,
I’ve got a question about a validation message.
Since the upgrade to version 2020.1 a validation message appeared which I don’t know how to interpret or solve. The validation says: ‘The cardinality of the look-up is invalid’ It has to do with the appearance of new columns in the look-up chain.
I have a table called object_part_service_task, which I want to limit to entries within the table object_component_service_task, so I created a look-up with the key containing system_company_id, object_component_id and service_task_id. The column service_task_id is the look-up column. The table object_component_service_task is limited to entries within the table object_class_service_task and has a look-up relationship with the key: system_company_id, object_type_id, object_class_group_id, object_class_id and service_task_id. This last table has a relationship with the table service_task, which is the table that contains all the service_tasks’ The relationship here is with system_company_id and service_taks_id.
In the previous version this look-up worked perfectly and even now the look-up is still working, but I do get this validation message. It is true that new columns were introduced within the chain, but that is necessary to get the correct relationship. So my question is: Why does this validation message appear? Everything seems to be working and in my opinion should be working. And how would I go about solving this validation message?
Page 1 / 1
Hi Mark,
Can you provide a screenshot of the data model, including the reference columns? This can be configured in the data modeler settings.
The problem that arises with invalid cardinality is that this allows one look-up key to have multiple translations. Sorting or filtering on such a value will not be possible and translating will be nondeterministic.
Hi Anne,
Thanks for your reply. I understand that with invalid cardinality you can have multiple translations, but I don’t quite see how this lookup has an invalid cardinality, since the relationships are defined by their primary keys.
The following is a screenshot of the data model:
I hope you can properly read it. I guess it is still readable if you zoom in.
Hi Mark,
This data model enforces that there may only be one object_type_id , object_type_class_group_id and object_class_id in for a certain object_component_service_task. So you are correct, the cardinality of the look-up is technically proper. There can only be one translation based on a system_company_id, object_component_id and service_task_id
However, the platform determines the cardinality a bit more simple: If any reference column is introduced at any point in the look-up chain, the cardinality is considered invalid.
This allows us to short-circuit a translation query directly to the translation table, without having to query any additional filter column values from intermediate tables.
As you can see, additional reference columns are introduced between object_class_service_task and object_class_service_task. This is why platform considers the cardinality invalid.
Granted - The cardinality would technically only be incorrect if the object_type_id, object_class_group_id and/or object_class_id in object_component_service_task were primary key columns. And since the extra introduced filter columns are omitted later in the look-up chain, the short-circut for translation queries would even be possible.
However, the look-up display resolvement is not that advanced at the moment.
My advice would be to include object_type_id, object_class_group_id and object_class_id all the way in the chain down to object_part_service_task.
Alternatively, you could use a view or look-up prefilter to limit the object_class_service_task records available to object_component_service_task and omit the object_type_id, object_class_group_id and object_class_id from the look-up reference to the view or prefiltered object_class_service_task table. However, this is only possible if the expression columns on object_component_service_task contain fixed values.
Hi Anne,
Thanks for your reply.
I must say that I don’t fully understand the necessity of this particular validation. I understand the fact that if you can reach 2 or more possible translation for the look-up in your look-up chain that you will have a problem that needs to be addressed, but in my opinion that would mean that each individual look-up should be evaluated and if they all prove to be correct then the look-up chain would also be correct. Since my look-ups all use the primary key, they all should prove to be correct. With this strategy you don’t need to validate the look-up chain, but simply validate each individual look-up.
In addition this validation is an error which means that I cannot simply approve the validation message, but need to solve it, while in fact there is nothing wrong with the model.
Your suggestion to use a prefilter and reduce the number of columns in the reference is actually creating a bad look-up reference, because the reference will undoubtedly find multiple translations. It is the prefilter that will reduce the number to 1, but this is something that the validation won’t detect.
I discussed your suggestions with a colleague of mine and your first suggestion has inspired us to come up with an alternative solution. Instead of moving the 3 new fields down to object_part_service_task, I have seen that I also can use a custom look-up and provide an alternative display column. Which means that I only need to move the display column from service_task to object_component_service_task. In my example I already had an expression field containing the look-up value of the service_task, which makes this task really simple.
I would much rather see however that this validation is either changed to a warning instead of an error, or preferably that the validation is changed to check individual look-ups instead of the look-up chain. Correcting the individual look-ups should also correct the look-up chain.
