Displaying 2 tables in 1 form

[Pripri804]

Hi,

I would like some help with the below please. I have an Azure SQL DB with 2 tables (amongst others):

dbo.CaseDetails = "ID", "IDClients" (amongst others)

dbo.Clients ="ID", "AccountRef" (amongst others)

dbo.casedetails.idclients is the ForeignKey for dbo.clients.id

Now, In the Form of my PowerApp where the items are displayed, in my case "Form_ViewCase", i would like to display the "AccountRef" rather than the ID, as per my SQL View above.

I tried the following formula

ThisItem.IDClients=LookUp('[dbo].[Clients]','[dbo].[Clients]'.ID='[dbo].[CaseDetails]'.IDClients,AccountRef)

and under the 1st '=' it's gicing me the following alert: 

"The values being compared have mismatched types (left is a number and right is text). Please check for logical and syntactis errors, and use parentheses to provide explicit logical grouping."

Here's a screenshot of it all:

Can anyone help me please? 

[BitLord69]

Are Clients.ID and CaseDetails.IDClients both int? A mismatch between thos two seems to be the beef PA has with the statement.

IDClients is probably a numeric field and AccountRef seems to be a text field. What happens if you change the field type of IDClients in the PA form to show text?

Next I would try is to move the AccountRef from inside the Lookup to outside, using a dot-notation

ThisItem.IDClients=LookUp('[dbo].[Clients]','[dbo].[Clients]'.ID='[dbo].[CaseDetails]'.IDClients).AccountRef

Good luck!

[Pripri804]

Hi @BitLord69

Thanks for your reply.

The Clients.ID and CaseDetails.IDClients are in fact both INT

Changed the formula as suggested and changed also the location of the formula: Before i wad adding it to the whole card, but now I added it to the label, to display text = still the same issue

[BitLord69]

Ah, I see it now!

Remove everything before Lookup().... You don't need to set the field (which is int, and the accountref text), you only want to display the text value that you get in return from the call to Lookup.

[Pripri804]

I understand your reasoning and I agree, however now I have another notification (which was present before as well) and an invalid argument error:

[BitLord69 · Accepted solution]

Ah... I see it again, and I should have thought about it in the last reply already.

When you do a Lookup, you have to look for a specific value and not the generic IDClients.

The Lookup should read:

LookUp('[dbo].[Clients]',ID=ThisItem.IDClients).AccountRef

You want to look up a specific record's clientid, and hence you have to use the record value, hence the ThisItem... 

Sorry I didn't see it before!

[Pripri804]

@BitLord69 Thank you so much !! Formula worked perfectly 

[BitLord69]

@Pripri804 You're welcome, glad I could help, eventually 🙂