Linked SQL Tables - how to relate their entities

Linked SQL Tables - how to relate their entities

  
Hello everyone,

I have two entities that I am using from two SQL linked tables (publishing the entities through integration studio).

One linked table "Request" and the other is "RequestStatus". The Request entity has a column/attribute "StatusId" of type "Int". The RequestStatus entity has an identifier the column/attribute "Id" of type "Int".

ServiceStudio treats the type of "RequestStatus.Id" as "RequestStatusIdentifier" while treats the data type of "Request.StatusId" as "Int".

When building my queries or expressions and I need to join both tables, the expression dialog objects for this condition
Request.StatusId = RequestStatus.Id
stating the data type RequestStatusIdentifier is not compatible with Int.

Is there a workaround to relate two linked entities like in this senario ??
Hello Shraim,
First of all, you can modify the data type of your Status ID in the Integration Studio..
As a workaround to use in the query you can use the built-in function IntegerToEntityRefInteger() to treat the integer as a key, ie: 

IntegerToEntityRefInteger(Request.StatusId) = RequestStatus.Id

Hope this helps.

Kind Regards,
Gonçalo Martins
Gonçalo Martins wrote:
Hello Shraim,
First of all, you can modify the data type of your Status ID in the Integration Studio..
As a workaround to use in the query you can use the built-in function IntegerToEntityRefInteger() to treat the integer as a key, ie: 

IntegerToEntityRefInteger(Request.StatusId) = RequestStatus.Id

Hope this helps.

Kind Regards,
Gonçalo Martins
 
 Thank you very much Goncalo. That function is exactly what I need to build the relation condition. For you other tip to change the data type in the integration studio, does that mean I can actually change the data type of Request.StatusId from Int to StatusIdentifier ?

Again..truly apprecate this help...now I am back on track with my first project :)
Shraim wrote:
 
 Thank you very much Goncalo. That function is exactly what I need to build the relation condition. For you other tip to change the data type in the integration studio, does that mean I can actually change the data type of Request.StatusId from Int to StatusIdentifier ?

Again..truly apprecate this help...now I am back on track with my first project :)
 
Hello Shraim,
Yes you can actually change the data type of your attribute in Integration Studio..
Just a warning..if you do that be careful when you need to refresh your entity in the extension because the data type can return to the data type integer (it might interpret it like that)..In this case you just have to change the data type each time you need to do that operation..
Good luck to your first project ;)


Kind Regards,
Gonçalo Martins

Gonçalo Martins wrote:
 
Hello Shraim,
Yes you can actually change the data type of your attribute in Integration Studio..
Just a warning..if you do that be careful when you need to refresh your entity in the extension because the data type can return to the data type integer (it might interpret it like that)..In this case you just have to change the data type each time you need to do that operation..
Good luck to your first project ;)


Kind Regards,
Gonçalo Martins
 
 Yes it worked beautifully. Thank you very much for the warning and for the generous help. Happy new year :)
Nice to know that it helps..
Happy New Year for you too..