Simple disambiguation question sql table fields in a concat,lookup

(0) Share

Report

Posted on by HansHeintz

711

Hi,

In code example underneath example a "dier" from sql table

'[dbo].[dier]'

has a "ras_id" and I want the name "naam" of the "ras" from the table

'[dbo].[ras]'

using a lookup on integer field "ras_id"
This goes wrong (same name every time). I try to use the disambiguation rules from te PA operators page but if I try things like

'[dbo].[dier]'[@ras_id] in stead of ras_id I get an error at the = operator and a delgation warning

I am clearly in need of some syntax education

;;UpdateContext(
 {
 ExportDiergegevens:
 Concat(
 Filter('[dbo].[dier]';TextInput16.Text in naam)
 ;Text(dier_id) 
 & ";" & Trim(Mid(naam;Len(TextInput16.Text)+1)) 
 & ";" & LookUp('[dbo].[ras]';ras_id=ras_id;naam)
 & Char(10) 
 
 ) 
 })

Categories:

Building Power Apps

All responses (4)

Answers (0)

Sort by

HansHeintz 711 on at

Like
a
(0)

Report
Copy link

Link copied!
I got it to work by putting both tables in collections (in this example the "ras" table is already cached into a collection, but still would like to know the right syntax with the sql tables

Reset(TextInput17) ;;UpdateContext({ExportDiergegevens:""}) ;;Reset(TextInput17) ;;ClearCollect(TargetDieren;Filter('[dbo].[dier]';TextInput16.Text in naam)) ;;UpdateContext( { ExportDiergegevens: Concat( TargetDieren ;Text(dier_id) & ";" & Trim(Mid(naam;Len(TextInput16.Text)+1)) & ";" & LookUp(CachedRas;CachedRas[@ras_id]=TargetDieren[@ras_id];naam) & Char(10) ) })
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @HansHeintz

As a general rule, I let ID be the name of the primary key in every table and use that table name in the foreign key in other tables. For example, If I have a Customer table and an Order table, the foreign key in the Order table is CustomerID. Then there is no ambiguity if I want to reference the Customer in an Order as Lookup(Customer, ID=ThisItem.CustomerID, CustomerName). Also since all primary keys are called ID, I don't have to remember it when I am doing a lookup or filter.
HansHeintz 711 on at

Like
a
(0)

Report
Copy link

Link copied!

Problem is that i am working with the sql database from another company and my app works alongside their desktop application. So their database structure is a given for me.
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@HansHeintz

Another option is to run a RenameColumns() function to disambiguate the column.