cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jesus_Aguirre
Advocate II
Advocate II

How can I compare two values ​​of two collect with their id?

Hello community, I have a question.

 

I have a problem when comparing two different values ​​of an inventory, I attach an image the example.

 

I have these two collects, how can I compare all the collect of deliveries by the id to know that the quantity of output is less than that of the inventory. (Like in the product6 there should be a mistake)

How could it be solved by means of a forall with if or something that can solve the problem.

 

Regards.

JA

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@Jesus_Aguirre 

Yes, but why a collection?  This is unnecessary overhead on your app!

The following will do the same, but no expensive collection overhead:

If(
    CountRows(
        Filter(Deliveries As _deliveries, 
            LookUp(Inventory, ID=_deliveries.ID && Quantity<= _deliveries.Quantity, true)
        )
    )>0, 
    Notify("Error",Error,5000)
)

 

AND, if you want to use a collection, don't double the processing time and waste the output of your ForAll (for all is not a For/Loop, it is a function that returns a table).  Also, your LookUp in your formula will fail to return the proper results.

So that formula would be:

ClearCollect(newcollect,
    ForAll(Deliveries As _deliveries, 
        If(LookUp(Inventory, ID=_deliveries.ID, Quantity<=_deliveries.Quantity, true) 
            {value:1}
        )
    )
);
If(CountRows(newcollect)>0,Notify("Error",Error,5000))

  

You could also go with this:

If(
    Len
        Concat(
            ForAll(Deliveries As _deliveries, 
                {value:
                    If(Quantity > LookUp(Inventory, ID=_deliveries.ID, Quantity<=_deliveries.Quantity, true), "T", "") 
                }
            )
        )
    )>0,
    Notify("Error",Error,5000)
)

 

And I could provide probably a couple dozen other ways.  That goes back to my original statement :

You are leaving plenty of ambiguity on the table.  I am not sure what your design is, and there are several ways to go about it.

😁

But in general...skip collections for doing something that you don't need them for.  They add overhead to your app and are usually not needed (although everyone seems to want to use them for everything!)

 

Happy Powerapping!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

11 REPLIES 11
RandyHayes
Super User
Super User

@Jesus_Aguirre 

What are you ultimately trying to do?  Are you trying to get a table of deliveries that would exceed inventory?

 

If so, then the following would provide you a table of "invalid" deliveries:

Filter(Deliveries As _deliveries, 
    LookUp(Inventory, ID=_deliveries.ID && Quantity<= _deliveries.Quantity, true)
)

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hi @RandyHayes 

 

I need to identify if there is any product in the entire delivery table that is greater than the inventory and notify the user. Do you have anything that can help me solve it?

 

Thanks and regards.

JA

RandyHayes
Super User
Super User

@Jesus_Aguirre 

You are leaving plenty of ambiguity on the table.  I am not sure what your design is, and there are several ways to go about it.

How are you wishing to notify the user?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@RandyHayes 

 

It is very simple, there is an output to some products and it is necessary to check that product output (collect deliveries) there is more product in the inventory, if it is correct you can send a notify of done, and in case there is not enough product in the inventory will send an error notify.

Note: it is necessary to review product by product if there is more quantity in the inventory.

  • Inventory is a collect
  • Deliveries is a collect

 

Regards.

JA

RandyHayes
Super User
Super User

@Jesus_Aguirre 

You are describing your workflow, but not describing your user interface.  

So, I will just provide some samples.

In this one there is a separate table of discrepancies based on the delivery and inventory tables:

RandyHayes_0-1612812305582.png

 

IN this one, we are just highlighting the items that are a discrepancy:

RandyHayes_1-1612812416890.png

 

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@RandyHayes 

 

In the first example of the image you mention it would be to create a collect according to that right? how could the code be?

In the second image it would be that if it finds one with that discrepancy, send an error message

Either of the two examples could help me to reach the solution, what could be the code?

 

Thanks for the support.

JA

RandyHayes
Super User
Super User

@Jesus_Aguirre 

The first image was done with the Formula I provided before:

Filter(Deliveries As _deliveries, 
    LookUp(Inventory, ID=_deliveries.ID && Quantity<= _deliveries.Quantity, true)
)

 

The second was done with adding an additional column to the table:

AddColumns(
    Deliveries As _deliveries,
    "over", LookUp(Inventory, ID=_deliveries.ID && Quantity<=_deliveries.Quantity, true)
)

Then this formula on the Template Fill property:

If(ThisItem.over, Red, Transparent)

And this formula on the Color property of all the labels in the gallery:

If(ThisItem.over, White, Black)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hello community.

 

Also use this code that can work:

ForAll(Deliveries,If(Quantity>LookUp(Inventory,ID=ID,Quantity),Collect(newcollect,{value:1})));If(CountRows(newcollect)>0,Notify("Error",Error,5000))

 

Regards.

JA

RandyHayes
Super User
Super User

@Jesus_Aguirre 

Yes, but why a collection?  This is unnecessary overhead on your app!

The following will do the same, but no expensive collection overhead:

If(
    CountRows(
        Filter(Deliveries As _deliveries, 
            LookUp(Inventory, ID=_deliveries.ID && Quantity<= _deliveries.Quantity, true)
        )
    )>0, 
    Notify("Error",Error,5000)
)

 

AND, if you want to use a collection, don't double the processing time and waste the output of your ForAll (for all is not a For/Loop, it is a function that returns a table).  Also, your LookUp in your formula will fail to return the proper results.

So that formula would be:

ClearCollect(newcollect,
    ForAll(Deliveries As _deliveries, 
        If(LookUp(Inventory, ID=_deliveries.ID, Quantity<=_deliveries.Quantity, true) 
            {value:1}
        )
    )
);
If(CountRows(newcollect)>0,Notify("Error",Error,5000))

  

You could also go with this:

If(
    Len
        Concat(
            ForAll(Deliveries As _deliveries, 
                {value:
                    If(Quantity > LookUp(Inventory, ID=_deliveries.ID, Quantity<=_deliveries.Quantity, true), "T", "") 
                }
            )
        )
    )>0,
    Notify("Error",Error,5000)
)

 

And I could provide probably a couple dozen other ways.  That goes back to my original statement :

You are leaving plenty of ambiguity on the table.  I am not sure what your design is, and there are several ways to go about it.

😁

But in general...skip collections for doing something that you don't need them for.  They add overhead to your app and are usually not needed (although everyone seems to want to use them for everything!)

 

Happy Powerapping!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (2,430)