cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pripri804
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
BitLord69
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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (71,854)