field disambiguation question

(0) Share

Report

Posted on by HansHeintz

711

In following code

ClearCollect(
 cAanwezig_asiel_verblijf
 ;AddColumns(
 Filter(
 FirstN(
 SortByColumns(
 asiel_verblijf
 ;"vertrek_datum"
 ;Ascending
 )
 ;2000
 )
 ;IsBlank(vertrek_datum) And !IsBlank(opname_datum)
 )
 ;"diernaam";LookUp([@dier];dier_id=asiel_verblijf.dier_id).naam
 ) 

 )

I want to lookup a record (sql table) with the same dier_id (an integer) in the table "dier" while building a collection from the table asiel_verblijf which also has a field dier_id.

The syntax above does not work (the lookup line) but is the syntax I would like to work if the world was round.

I tried disambiguation syntax in the info from Microsoft MyTable[@fieldname]

dier_id=asiel_verblijf[@dier_id]

but it results in a delegation error plus a real error saying asiel_verblijf_id[@dier_id] is a table (???????) so i cannot compare it to dier_id.

What is the magic syntax here?

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Sort by

Krishna_R 525 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @HansHeintz
give a try like this
LookUp(dier,dier_id=asiel_verblijf[@dier_id])

Was this reply helpful? Yes No
HansHeintz 711 on at

Like
a
(0)

Report
Copy link

Link copied!

That is what I did, see 4th line from below
dier_id=asiel_verblijf[@dier_id]
It will not work. Maybe it does not want to see the table anymore through the addcollumns construction
Maybe I do addcolumns(addcolumns(..;"dier_d2":dier_id);"diernaam":Lookup(....dier_id=dier_id2)
A bit convoluted it that is the right english word.

Was this reply helpful? Yes No
HansHeintz 711 on at

Like
a
(0)

Report
Copy link

Link copied!
So convoluted it is..

;;ClearCollect( cAanwezig_asiel_verblijf ;AddColumns( AddColumns( Filter( FirstN( SortByColumns( asiel_verblijf ;"vertrek_datum" ;Ascending ) ;2000 ) ;IsBlank(vertrek_datum) And !IsBlank(opname_datum) ) ;"dier_id2";dier_id ) ;"recDier";LookUp([@CachedLast4000Dieren];dier_id=dier_id2) ) )

This code adding a column results in error number is being compared to table

;"recDier";LookUp([@CachedLast4000Dieren];dier_id=asiel_verblijf[@dier_id])

Was this reply helpful? Yes No
KvB1 1,596 on at

Like
a
(0)

Report
Copy link

Link copied!

I think the problem here is that asiel_verblijf is a table, and when you use asiel_verblijf[@dier_id] you are referring to a column in that table, which would still be a table.

I'm not sure what you want to achieve. You want to grab the naam value from CachedLast4000Dieren for every record in asiel_verblijf, based on a shared unique ID?

Was this reply helpful? Yes No
HansHeintz 711 on at

Like
a
(0)

Report
Copy link

Link copied!

I am just playing around to get a chached animal table from a table with 50000 animals that contains the ca. 425 animals current in an animal shelter from 2 sql tables. One containing all animals, one containg all stays of all animals. Both sql tables have their records id-ed with an integer. All this in a quick way.
What I do now is cache the last 4000 animals since most present animals will also have their first stay.
I filter all stays without a date of leaving to a collection and add the animals (records) as a column from the cached table. Any animals I have missed (without a animal record in the end, currently only 7 for example) I lookup the record in the original table.
I then add all sorts of other columns from cached lookup tables like breed, species, color etc.
This works perfectly and fast, all caching is done in just a few seconds. I can now search all present animals lighning fast for breeds colors etc.
So I have no problem achieving my goals I am just disappointed about the efficiency of the code which with I can achieve it and the fact that according to my understanding of microsofts syntax explanation things are not working as expected. Generally I am disappointed with how many times I have to struggle to find the right syntax to do simple lookups; id (guid) searches in dataverse tables for example.
Generally I like my poweraps work, otherwise I would not bother to complain 😉

The reason I ask questions is that sometimes the code I have to write seems so illogical that I think I am doing something wrong and there has to be simpler way which i would like to learn.

Was this reply helpful? Yes No