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

[Jesus_Aguirre]

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

[RandyHayes]

@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.

[Jesus_Aguirre]

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]

@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?

[Jesus_Aguirre]

@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]

@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:

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

[Jesus_Aguirre]

@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]

@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)

[Jesus_Aguirre]

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 · Accepted solution]

@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!