cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver II
Resolver II

Simple disambiguation question sql table fields in a concat,lookup

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) 
                       
                     ) 
              })

 

 

 

 

4 REPLIES 4
Highlighted
Resolver II
Resolver II

Re: Simple disambiguation question sql table fields in a concat,lookup

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) 
                       
                     ) 
              })

 

Highlighted
Super User
Super User

Re: Simple disambiguation question sql table fields in a concat,lookup

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. 

Highlighted
Resolver II
Resolver II

Re: Simple disambiguation question sql table fields in a concat,lookup

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.

Highlighted
Super User
Super User

Re: Simple disambiguation question sql table fields in a concat,lookup

@HansHeintz 

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

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 (5,775)