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

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (2,121)