cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate III
Advocate III

Displaying 2 tables in 1 form

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

 

pic01.png

 

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:

 

pic03.png

 

Can anyone help me please? 

1 ACCEPTED SOLUTION

Accepted Solutions

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!

View solution in original post

7 REPLIES 7
Power Participant
Power Participant

 

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!

Hi @BitLord69

 

Thanks for your reply.

 

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

 

pic04.png

 

 

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

 

pic05.png

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.

 

 

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

 

pic06.png

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!

View solution in original post

@BitLord69 Thank you so much !! Formula worked perfectly 

 

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (7,580)