cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Power Participant
Power Participant

Re: Displaying 2 tables in 1 form

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
Highlighted
Power Participant
Power Participant

Re: Displaying 2 tables in 1 form

 

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!

Highlighted
Advocate III
Advocate III

Re: Displaying 2 tables in 1 form

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

Highlighted
Power Participant
Power Participant

Re: Displaying 2 tables in 1 form

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.

 

 

Highlighted
Advocate III
Advocate III

Re: Displaying 2 tables in 1 form

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

Highlighted
Power Participant
Power Participant

Re: Displaying 2 tables in 1 form

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

Highlighted
Advocate III
Advocate III

Re: Displaying 2 tables in 1 form

@BitLord69 Thank you so much !! Formula worked perfectly 

 

Highlighted
Power Participant
Power Participant

Re: Displaying 2 tables in 1 form

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

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

Top Solution Authors
Top Kudoed Authors
Users online (10,619)