500 record limit again in Azure SQL Database

[hotely]

Hello,

i know this is something often discussed, but need a feedback from the experts.

I have a simple scenario three Tables in a Azure SQL Database

1) Opportunity (150 Records for now...)

2) Name (133 Records for Now)

3) Place (8000 Records)

Opportunity  has column name NameID with foreign Key to Name Table.

Name has a column PlaceID foreign Key to Place Record.

I want to create a Gallery with the Opportunity Data including The Place Name (column of Place Table).

I have the blue dot problem, I guess only solution is to create a collection of Place is it correct?

thanks

[Meneghino]

Hi @hotely

Yes, creating a collection with all 8000 records of place would resolve your issue but it will be a pain.

I have the impression that soon the in operator will be delegated, so that the pain should go away.

However, I am pretty sure that you may get away with actually doing something like this.  I just tried it and it worked for me in a similar situation.

1) Set the Items of your gallery to something like this:

AddColumns(Opportunity, "Name", LookUp(Name, ID = NameID))

2) In the gallery template create a label with Text as follows:

LookUp(Place, ID = ThisItem.Name.PlaceID, PlaceName)

Please let me know how you get on

[hotely]

Hi @Meneghino

thank you so much for your prompt reply. I have not been able to send my notes to you.

Here Few notes:

Why it's a pain to create a collection of 8000 records?

1) I don't understand the sintax what you want to add to opportunity a column or an object? I'm pretty new t PowerApps but the sintax looks wrong. Can you please check?

2) The way you are using ThisItem.Name.PlaceID it's strange to me since Name is a Column and not an object you can reference ang get the Properties like Entiy Framework, but again I'm pretty new to PowerApps.

Thank again Meneghino for your help to me and the others in the community.

[Meneghino]

Hi @hotely

---

@hotely wrote:

Hi @Meneghino

thank you so much for your prompt reply. I have not been able to send my notes to you.

Here Few notes:

Why it's a pain to create a collection of 8000 records?

Because of delegation you can only query 500 records at a time, so you would need a loop to run 16 times to get the items into the collection.  It is possible, but messy.

1) I don't understand the sintax what you want to add to opportunity a column or an object? I'm pretty new t PowerApps but the sintax looks wrong. Can you please check?

We add a column called Name but the column contains a record (what is returned by the Lookup)

2) The way you are using ThisItem.Name.PlaceID it's strange to me since Name is a Column and not an object you can reference ang get the Properties like Entiy Framework, but again I'm pretty new to PowerApps.

ThisItem.Name.PlaceID returns the value of PlaceID in the record contained in the Name column of the current item

Thank again Meneghino for your help to me and the others in the community.

---

[hotely]

Hi @Meneghino,

thank you so much! Great explanation, i moved on but I have another little issue.

I managet to add a Record Name to Opportunity, following your instructions:

1) Added to Gallery Items the following:

AddColumns(Opportunity; "Name"; Lookup (Name; NameID = NameID))

2) Now I have a Label in the Gallery of Opportunity that contains as you suggested

Lookup(Place; ThisItem.Name.PlaceID = PlaceID; Name)

Now the problem is that all the Records in Opportunity Shows the same Place, It looks like I added to each opportunity record the Same Name Record (actually the first one).

Thanks again

Any idea?

[Meneghino]

Hi @hotely, the problem is here:

AddColumns(Opportunity; "Name"; Lookup (Name; NameID = NameID))

I originally suggested ID=NameID, but in any case the first needs to be the ID column name of the Name table, whereas the second needs to be the foreign key of the Opportunity table that references the Name table.

Please let me know if you resolve it.

[hotely]

Hi @Meneghino

not your fault, of course you didn't know which was the name of the Primary Key of the Name Table. So i thought 

the following is right and i used this:

AddColumns(Opportunity; "Name"; Lookup (Name; NameID = Opportunity.NameID))

any idea why it's not working?

Do i need to so something like:

AddColumns(Opportunity; "Name"; Lookup (Name; NameID = Opportunity.NameID))

Thanks again!

[Meneghino]

https://powerapps.microsoft.com/en-us/tutorials/operators/#disambiguation-operator

AddColumns(Opportunity; "Name"; Lookup (Name; Name[@NameID] = Opportunity[@NameID]))

Please let me know

[hotely]

Hi Meneghino,

thanks again, the formula worked, but i get the Blue dot, so i think I'm stacked, any idea?

Thanks

[Meneghino]

Hi @hotely

Where do you get the blue dot?

Can you provide a screenshot?