Need Help formula ForAll

[PowerApps11]

Hello Community, 

I have a gallery with all outstanding and wanted to take payment, via payment box outside named amount received, I need when I add a value for example: there is two records in the gallery the first record open balance is $100 and second record $50

now if I add amount received box a $100 I would to apply that amount to the oldest record and then amount left to the latest record as the value entered was not enough to clear both records 
Is there a way to do that?

I have tested this but it patches minus second record and not by order

ForAll(
    galPayment.AllItems As aPayment,
    Patch(
        ProjectPayment,
        LookUp(
            ProjectList,
            ID = aPayment.ID  And 'Invoice Number' = aPayment.'Invoice Number'
        ),
        {Balance: aPayment.Balance - txt_OrderSalespayment.Text}
    )
)

When used above formula will patch like wrong as you can see screenshot below it also minus the other record  -$50

[WarrenBelz]

Hi @PowerApps11 ,

I am a little unclear on your logic here as both records in your example have the same Invoice No and after reading several times, I still possibly cannot fully grasp what you are trying to do. Assuming these are separate invoices and you have received an amount of money, are you trying to apply it to the oldest first, then any remaining to the next oldest and so on until the payment is fully deducted ? Also do you have several entries for the same Invoice No (the ID will always be unique, so using that is a waste of time ?). Also I assume you are selecting the invoice displayed in the gallery prior.

[PowerApps11]

Hi @WarrenBelz 

Both are the same invoices, all I need is when added the amount outside amount received to spread the amount both records and if it is not full amount, to apply one and left over amount to apply another record.

The gallery is showing same invoice records only already . 

[WarrenBelz]

@PowerApps11 ,

So I your example, it would have zeroed the first one and left the second one at $50 ?

[PowerApps11]

Hi @WarrenBelz 

That's exactly what I need thanks 

[WarrenBelz]

@PowerApps11 ,

Why are you looking up a different data source to what you are patching to (Project Payment and ProjectList)? You cannot do this as you will not find any matching records.

[PowerApps11]

@WarrenBelz 
Not looking another list just copied wrongly here the one using in my app 

ForAll(
    galPayment.AllItems As aPayment,
    Patch(
        ProjectPayment,
        LookUp(
            ProjectPayment,
            ID = aPayment.ID  And 'Invoice Number' = aPayment.'Invoice Number'
        ),
        {Balance: aPayment.Balance - txt_OrderSalespayment.Text}
    )
)

As you see my screenshot wanted to zero the first one record as it is $50, course it will not cover the amount I have applied. currently what is doing it will apply the $100 and also apply the other record that's why you see -$50 

[PowerApps11]

@WarrenBelz 

I suspecting will need to use IF Conditional statement Balance part but don't know how to go about 

Balance: aPayment.Balance - txt_OrderSalespayment.Text

[WarrenBelz · Accepted solution]

@PowerApps11 ,

What you have is far from straight-forward - you need to loop through the Gallery and incrementally reduce the balances until you run out of money. You cannot set a Variable inside a ForAll() statement, therefore need to utilize a Collection referencing the last item each time. I have this working here on a model

ClearCollect(
   colPayment,
   {Payment: Value(txt_OrderSalespayment.Text)}
);
ForAll(
   galPayment.AllItems As _Invoices,
   With(
      {
         _Unallocated: Last(colPayment).Payment,
         _Invoice: _Invoices.Balance
      },
      With(
         {
            _Allocate: 
            If(
               _Unallocated > _Invoice,
               0,
               _Invoice - _Unallocated
            ),
            _Remaining: 
            If(
               _Unallocated > _Invoice,
               _Unallocated - _Invoice,
               0
            )
         },
         Patch(
            ProjectPayment,
            LookUp(
               ProjectPayment,
               ID = _Invoices.ID And 'Invoice Number' = _Invoices.'Invoice Number'
            ),
            {Balance: _Allocate}
         );
         Collect(
            colPayment,
            {Payment: _Remaining}
         )
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[PowerApps11]

Hi @WarrenBelz 

@WarrenBelz  I can’t thank you enough and I really appreciate your help You’re a lifesaver