cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hotely
Frequent Visitor

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 Community Champion
Community Champion

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 Community Champion
Community Champion

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
Frequent Visitor

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 Community Champion
Community Champion

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
Frequent Visitor

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 Community Champion
Community Champion

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
Frequent Visitor

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 Community Champion
Community Champion

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

 

Highlighted
hotely
Frequent Visitor

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 Community Champion
Community Champion

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
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (6,085)