Add Credit memo

[PowerApps11]

Hi

I am hoping that someone can help me with a personal project I am working on. I recently tasked to create a credit memo to issue for the customers so far I have implemented a way to patch SharePoint about the credit memo the amount. But I have some problem following below,
I would like sum total minus the credit memo and patched

Thanks 

Let me know if i have to change a different approach 

[poweractivate]

@PowerApps11 

Seems  you are trying to work with credit memos and would like to subtract the credit memo amount from the sum total and then patch this information to SharePoint.

Here's a general approach you might take, assuming you are working within Power Apps Canvas App and using Power Fx.

Please note that more specific details about your SharePoint list structure, the way you're tracking amounts, and more details of the application may be necessary, for a more tailored solution.

1. Retrieve Data: Use the LookUp or Filter function to retrieve the specific customer record and related credit memo information from your SharePoint list.
2. Calculate the New Amount: Subtract the credit memo amount from the sum total. You might use a formula similar to this:

Set(varNewAmount, varSumTotal - varCreditMemoAmount)

3. Patch the New Amount to SharePoint: Use the Patch function to update the record with the new amount. You can try to use the 2-argument version of Patch, when patching one record to data source (when ID is provided, it changes it - when it's not provided, it creates it):

Patch(splistdatasource, {ID: varItemToChange.id, ColumnToChange: varNewAmount})

4. Error Handling and Validation: Implement any error handling and validation that may be needed.

If you want some more info, please provide more details about the structure of your SharePoint list, how you are tracking the sum total and credit memo amounts currently, and any specific business rules you need to enforce. That way, maybe we can provide a more detailed and tailored solution.

See if it helps as starting point @PowerApps11 

[PowerApps11]

@poweractivate 
Thanks for your reply @poweractivate  , my SharePoint list structure I have a column name CreditAmount and CreditMemoUsed which is single text line wanted if the credit issued to mark Yes to CreditMemoUsed that way I can track issued or not 

Here is the problem facing, when I want to issue the credit I have a gallery filtered by  

Filter(
    Orders,
    ID = CurrentItemOrders.ID Or CustomerID = CurrentItem.ID,
    Status = "Open" ||  TransactionType = "Credit Memo" && CreditMemoUsed = "No"
)

Which show all those status open and transaction type = credit memo and CreditMemoUsed  = No

My goal to achieve in this screen when selected one of the record or all to subtract the credit memo amount that available for this particular customer and patch, please correct me if in anyway am doing wrong or my structure incorrect. 

Thanks 

[PowerApps11]

@poweractivate 

Please you can tell me the best approach? and how will do it .

Thanks 

[poweractivate]

1. First, you can collect the base records, which are the selected items in the gallery.
2. Next, you can collect the change records by going through the base records, calculating the new amount, and updating the CreditMemoUsed field, then using the output of ForAll into a collection to serve as the changed records.
3. Then you can use the Patch function with the base records and change records to update the SharePoint list.

Here's a formula example:

ClearCollect(colBaseRecords, Gallery.SelectedItems); // Collect selected items from gallery

ClearCollect(
    colChangeRecords,
    ForAll(
        colBaseRecords,
        {
            TotalAmount: ThisRecord.TotalAmount - ThisRecord.CreditAmount,
            CreditMemoUsed: "Yes"
        }
    )
); // Collect changes

Patch(
    Orders,
    colBaseRecords,
    colChangeRecords
); // Patch updated info to SharePoint

In this example:

• colBaseRecords collects the selected records from the gallery that you wish to update.
• colChangeRecords collects the changes you want to apply to those records. For each record, it subtracts the credit amount from the total amount and sets CreditMemoUsed to "Yes."
• The Patch function then takes both of these collections and applies the changes to the SharePoint list.

See if it helps @PowerApps11 

[PowerApps11]

@poweractivate 
Maybe am doing wrong is only collecting one record at moment as I have other records with same orderID which the customer has open TotalAmount so my goal is example customer A has 4 records totalAmount 500 and giving Credit Memo Amount of 200 so when I come to  payment gallery which I'm already filtering records open and CreditMemoUsed ="No" and I  have checkbox inside the payment gallery when I check the checkbox need it to subtract the credit memo amount available to customer and if it is not enough only subtract what's available credit or if the totalAmount is less than the credit just subtract whatever the totalAmount and rest credit keep it for next payment.

Thank you so much for your help .

[poweractivate · Accepted solution]

Try like this @PowerApps11 

1. Calculate Total Credit Available: Determine the total credit memo amount available for the specific customer.

2. Collect All Relevant Orders: Collect all relevant orders for the customer that are open and have not yet used the credit memo.

This formula should gather all the open orders for a specific customer where the credit memo has not been used.

ClearCollect(
    colOpenOrders,
    Filter(
        Orders,
        CustomerID = CurrentItem.ID,
        Status = "Open" || TransactionType = "Credit Memo" && CreditMemoUsed = "No"
    )
);

3. Prepare Changes to Apply Credit: Go through the collected records, calculate the new amounts and mark credit memos as used, and collect these changes into a new collection.

This formula goes through the open orders, applies the credit memo amount accordingly, and collects the changes into a new collection.

ClearCollect(
    colChangeRecords,
    ForAll(
        colOpenOrders,
        {
            TotalAmount: ThisRecord.TotalAmount - Min(ThisRecord.TotalAmount, varTotalCreditMemo),
            CreditMemoUsed: "Yes"
        }
    )
);

4. Apply Changes to SharePoint: Use the Patch function with the base records (open orders) and change records (calculated changes) to update the SharePoint list.

This formula takes the open orders and the changes and applies them to the SharePoint list, updating the records accordingly.

Patch(
    Orders,
    colOpenOrders,
    colChangeRecords
); // Patch updated info to SharePoint

This should apply credit memos to the relevant orders.

See if this helps @PowerApps11 

[PowerApps11]

@poweractivate 

Sorry for the delay replying was on holiday but when I run last time it is just adding up the credit even after issue and marked "Yes", as right now I need to do something else so will mark a solved but if need more assistance will post again thanks for your time.