cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hotely
Level: Powered On

500 record limit again in Azure SQL Database

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

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Meneghino
Level 10

Re: 500 record limit again in Azure SQL Database

Hi @hotely

I understand that Name has 133 rows, so until you get to 500 rows then the blue dot is not a problem.

In any case, PA Studio may be confused by the disambiguation operator, so you could try this syntax:

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

This should still work, but may remove the blue dot.

If it does not remove the blue dot or does not work, then you could try to change one of the column names so that you do not need disambiguation.  This may remove the bule dot, but not guaranteed.

Please let me know

View solution in original post

14 REPLIES 14
Meneghino
Level 10

Re: 500 record limit again in Azure SQL Database

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
Level: Powered On

Re: 500 record limit again in Azure SQL Database

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
Level 10

Re: 500 record limit again in Azure SQL Database

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
Level: Powered On

Re: 500 record limit again in Azure SQL Database

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
Level 10

Re: 500 record limit again in Azure SQL Database

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
Level: Powered On

Re: 500 record limit again in Azure SQL Database

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
Level 10

Re: 500 record limit again in Azure SQL Database

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

 

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

Please let me know

 

hotely
Level: Powered On

Re: 500 record limit again in Azure SQL Database

Hi Meneghino,

 

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

 

Thanks

Meneghino
Level 10

Re: 500 record limit again in Azure SQL Database

Hi @hotely

Where do you get the blue dot?

Can you provide a screenshot?

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (9,964)