I have the following scenario: a user entity and another relationship entity.
I linked these two entities in a way that the relationship entity has two keys
foreign to the user table, then this relationship table has 2 fields, apart from the ID,
fk_usuario_01 and fk_usuario_02. And in the user table, I have other cadastral fields and a field
with the name is_estab and it is in this field that I differentiate the records that are associated in each field
relationship entity, in this case, if a user entity record has this is_estab field as true,
then this record is associated in the field fk_usuario_02, if false, the record is associated in the field fk_usuario_01.
That way, I can have a relationship with the same fk_usuario_01 and several fk_usuario_02 and
I can also have the same fk_usuario_02 for several fk_usuario_01.
In addition, in this user entity, it has an identifier list, which serves to indicate whether this record is a user, a customer or a supplier, and that user can have more
of an identifier, so the field is of the list type. In this case, if the identifier is 1, then he is a user,
if it's 2, it's a customer, if it's 3 it's a supplier.
The problem I'm having is this: I need to show a list of relationships on the screen.
But I need to filter, for example if fk_usuario_02 has id 3;
if fk_usuario_01's identifier list has identifier 2, in a way that does not return records
repeated. It is necessary to make this consultation with XData.
As I am doing, duplicate entries are returned:
'$ filter = (FkUsuario01 / IdentifierList / Identifier eq 2) and (FkUsuario02 eq 3)'
I have 15 records in that relationship table. The query should only return 5,
but 35 is returned. Can you explain to me how to make this consultation?