Payments Lookup

[Ramole]

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 

[v-jefferni]

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.

[Ramole]

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  

[v-jefferni]

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.

[Ramole]

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 

[v-jefferni]

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.

[Ramole]

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
        )
);

[v-jefferni]

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.

[Ramole]

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'
)

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

[v-jefferni]

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.