cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ramole
Post Prodigy
Post Prodigy

Payments Lookup

Hi

I have a contact list and within there a field called Credit Line which is Number field, and normally what i do is whenever i use another list Loan Managment  i lookup the credit list of the customer and i get the that way but now i would like also check if the customer used all the credit line allocated within the Loan Management list, something like please

 

Thank you 

Thank you
1 ACCEPTED SOLUTION

Accepted Solutions
v-jefferni
Community Support
Community Support

Hi @Ramole ,

 

I think you could leverage a Abs() function to make the value always be positive numbers to submit.

 

ForAll(
        EditTeamInfoLinkGallery_2.AllItems,
   Patch(
      'Loan Management',
      Defaults('Loan Management'),
       {
                Company: drpLoanCustomer.Selected,
                Payment_x0020_frequency: drpLoanPaymentFreq.Selected,
                InvoiceNumber: txtLoanInvoice.Text,
                LoanRequestedDate: dateLoanRequestDate.SelectedDate,
                LoanDueDate: dateLoanDueDate.SelectedDate,
                LoanAmount: Abs(Value(txtinpLaonAmount.Text)),   //Use Abs function
                Status: drpLoanStatus.Selected,
                Notes: txtinpLoanNotes.Text,
                Title: txtLoanTitle
            },
            FormLoan.Updates
        );
   Patch(Contact,
       LookUp(Contact,
             ID = drpLoanCustomer.Selected.Id),
             {'Total Loan Amount': (LookUp(Contact,
             ID = drpLoanCustomer.Selected.Id).'Total Loan Amount'-Abs(txtinpLaonAmount.Text))}     //Here Abs function, too
      )
   )
);

 

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

14 REPLIES 14
v-jefferni
Community Support
Community Support

Hi @Ramole ,

 

Could you please share more details about your scenario? What's the layout of your App? What columns are there in the Loan Management list? What would you like to display with the checking result?

 

Assuming that there is a column named like "Amount" which stores the amount of each loan note, then you could get the total loan amount of the customer through below formula in Text property of a Label:

Sum(Filter('Loan Management',Contactor.Value= ContactorName),Amount)

But this kind of formula is non-delegable which means it won't work if the Loan Management list holds more than 2000 records.

 

I think you could consider add a column in Contactor list which one could name as "Total Loaned Amount" to store the total amount, then the comparation would be quite simple.

 

If this did not solve your question, please provide more details.

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi @v-jefferni 

I have added Total Loaned Amount in contact list how would i compare how much credit is left for the customer please 

 

Thanks  

Thank you
v-jefferni
Community Support
Community Support

Hi @Ramole ,

 

That depends on what would you like to display. And first of all, you need to fill the "Total Loaned Amount" column with actual number of each customer. 

 

After fulfilled above operation, assuming you would let users to see customers in the Gallery of Loan Managment, you could set the Label's Color to:

If((LookUp(Contractor,Name=Self.Text).'Credit Line'-LookUp(Contractor,Name=Self.Text).'Totle Loan Amount')>0,Green,Red)

So if  "the customer used all the credit line allocated within the Loan Management list", the text of customer name would show in red, else would show in green. You could also add a Label beside the customer name label and set the Text to below formula to display the credit balance:

Text(LookUp(Contractor,Name= ThisItem.Contract.Value).'Credit Line'-LookUp(Contractor,Name=ThisItem.Contract.Value).'Totle Loan Amount')

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi @v-jefferni 

 

Thanks, for last can you please tell how can I patch the contact list Total Loan Amount field only according to the Name when creating new loan from loan Management List please 

 

Thank you so much 

Thank you
v-jefferni
Community Support
Community Support

Hi @Ramole ,

 

Do you create new loan with EditForm?

 

If so, there would be definitely a loan amount or something like to fill the loan amount of the selected customer, right? So you could apply a Patch function on the Form's submit button after the SubmitForm function:

Patch(Contact,
      LookUp(Contact,
             ContactName = ContactDataCardValue.Selected.Value),
             {'Total Loan Amount': (LookUp(Contact,
             ContactName = ContactDataCardValue.Selected.Value).'Total Loan Amount'-'Total Loan Amount'DataCardValue.Text)}              
      )
)

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi @v-jefferni 

I normally use patch here is my existing formula 

ForAll(
        EditTeamInfoLinkGallery_2.AllItems,

   Patch(
      'Loan Management',
      Defaults('Loan Management'),
       {
                Company: drpLoanCustomer.Selected,
                Payment_x0020_frequency: drpLoanPaymentFreq.Selected,
                InvoiceNumber: txtLoanInvoice.Text,
                LoanRequestedDate: dateLoanRequestDate.SelectedDate,
                LoanDueDate: dateLoanDueDate.SelectedDate,
                LoanAmount: Value(txtinpLaonAmount.Text),
                Status: drpLoanStatus.Selected,
                Notes: txtinpLoanNotes.Text,
                Title: txtLoanTitle
            },
            FormLoan.Updates
        )
);
Thank you
v-jefferni
Community Support
Community Support

Hi @Ramole ,

 

Basically the same, put my formula after your Patch part:

ForAll(
        EditTeamInfoLinkGallery_2.AllItems,
   Patch(
      'Loan Management',
      Defaults('Loan Management'),
       {
                Company: drpLoanCustomer.Selected,
                Payment_x0020_frequency: drpLoanPaymentFreq.Selected,
                InvoiceNumber: txtLoanInvoice.Text,
                LoanRequestedDate: dateLoanRequestDate.SelectedDate,
                LoanDueDate: dateLoanDueDate.SelectedDate,
                LoanAmount: Value(txtinpLaonAmount.Text),
                Status: drpLoanStatus.Selected,
                Notes: txtinpLoanNotes.Text,
                Title: txtLoanTitle
            },
            FormLoan.Updates
        );
   Patch(Contact,
       LookUp(Contact,
             ID = drpLoanCustomer.Selected.Id),
             {'Total Loan Amount': (LookUp(Contact,
             ID = drpLoanCustomer.Selected.Id).'Total Loan Amount'-txtinpLaonAmount.Text)}              
      )
   )
);

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi @v-jefferni 

You right I did that but is this correct or i am doing something wrong on calculation please see the screenshot 
Loan management that's what it shows and the formula I used 

Text(
    LookUp(
        Customers,
        Company = Gallery2_11.Selected.Company.Value
    ).UserCredits - LookUp(
        Customers,
        Company = Gallery2_11.Selected.Company.Value
    ).'Total Loaned Amount'
)


CaptureLoanLinecredit.PNG

 

Contact List Total Loaned -10 from user credit on PowerApps it should say 4 remining ?
CaptureLineContacts.PNG

Thank you
v-jefferni
Community Support
Community Support

Hi @Ramole ,

 

I don't think the loaned amount could be a negative number, and the logic is fine since 14- (-10) = 24 is correct.

 

Or if you set the loaned amount all be negative numbers, you could change minus to plus in the formula.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (76,250)