Multiple record entries from one form using a loop calculation.

[rlavigne99]

Hello All, 

I have a simple form. (see below) 

I am trying to make a prepaid contract schedule by month. So I need to take the invoice amount, divide by the contractInMonths to give me the monthlypayment per month over the life of the contract. (below you can see that we are ok there - no issue) 

I then need to take that monthly payment (lets say a 12 month contract) and make 12 entries into my prepaid schedule table. This is where I run into an issue. I think that I need to create a collection for the 12 entries and use a ForAll to enter each one into the database but I am not sure how to use the ForAll to make the collection in the first place. I am still new to powerapps so any help would be great. Also, don't be scared to tell me that I am approaching this completely wrong as well 🙂 

So I know how to make the variable for the contract in months and how to add a month to the invoice date, but I dont know how to loop it all to get these 12 transactions into the DB. 

[rlavigne99]

I feel like I need to use some sort of ForAll() with a Sequence() but not sure how to put that together. 

[BCLS776 · Accepted solution]

Try something that looks like this:

ForAll(Sequence(Value(ContractInMonths.Text)) As Index,
    Patch(myPrepaidScheduleTable, Defaults(myPrepaidScheduleTable),
    {
        DueDate: DateAdd(Today(),Index.Value,Months),
        PaymentAmount: MonthlyPmt.Text,
        Vendor: Vendor.Text
    }
    )
)

Note, I've made assumptions about column names and control names in your app, but the format will be similar. As written, this code will create the number of records specified in ContractInMonths.Text, and populate three columns in that record: The payment amount, the vendor name, and due dates calculated by increasing months past today.

Hope that helps,

Bryan

[rlavigne99]

Am going to try this asap. Thank you so much. I will be sure to update this feed tomorrow with the results. 

[rlavigne99]

Patch('ITCosting PrePaid Schedules', Defaults('ITCosting PrePaid Schedules'),
{
        PmtMonthDate: invoicedatevalue.SelectedDate,
        InvoiceAmt: Value(invoiceAMTvalue.Text),
        Vendor: vendornamevalue.Text,
        ContractInMonths: Value(contractMonths.Text),
        ExpenseType: expensetypevalue.Text, 
        RenewalDate: renewdatevalue.SelectedDate,
        InvoiceNumber: invoicenumbervalue.Text,
        InvoiceDate: invoicedatevalue.SelectedDate,
        MonthlyPmt: Value(monthlypaymentvalue.Text)
    }
);


ForAll(Sequence(Value(contractMonths.Text - 1)) As Index,
    Patch('ITCosting PrePaid Schedules', Defaults('ITCosting PrePaid Schedules'),
    {
        PmtMonthDate: DateAdd(Today(),Index.Value,Months),
        InvoiceAmt: Value(invoiceAMTvalue.Text),
        Vendor: vendornamevalue.Text,
        ContractInMonths: Value(contractMonths.Text),
        ExpenseType: expensetypevalue.Text, 
        RenewalDate: renewdatevalue.SelectedDate,
        InvoiceNumber: invoicenumbervalue.Text,
        InvoiceDate: invoicedatevalue.SelectedDate,
        MonthlyPmt: Value(monthlypaymentvalue.Text)
    }
    )
);  

So here is the code that I got working with your suggestions. It seems to be doing exactly what I wanted now. Thank you so much for your assistance. I also learned some stuff here. So thanks for that as well.