cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rlavigne99
Helper I
Helper I

Multiple record entries from one form using a loop calculation.

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 🙂 

 

rlavigne99_0-1632086401876.png

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_1-1632086802624.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
BCLS776
Super User
Super User

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

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

View solution in original post

4 REPLIES 4
rlavigne99
Helper I
Helper I

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

BCLS776
Super User
Super User

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

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.
rlavigne99
Helper I
Helper I

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

rlavigne99
Helper I
Helper I

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. 

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (4,710)