Answered

Create Payment register

(0) Share

Report

Posted on by Ramole Super User 2024 Season 1

I have a SharePoint list i am trying to do if possible i can register payments example i have a field FreightCharges and if i add $600 and after a couple days if i want to pay partial payment of $400 and balance will be $200 now i need the partial payment should register so I can check later how many partial payment I made on same person please help

Thanks

My List

CollectonDelivery Yes/No Item

CompanyName Lookup Item

Order date Date and Time Item

Shipping Method Choice Item

TrackingNumber Single line of text

TotalPieces Number
Weight Single line of text
Length Single line of text
DeclaredValue Currency
CODAmount Currency
CODType Choice
FreightCharges Currency
Width Single line of text
Height Single line of text

Categories:

Building Power Apps

All responses (9)

Answers (1)

Sort by

CU-18081211-6 9,272 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Ramole ,

If think that you can take in consideration to create an additional list where you keep the payment records history. The new list will be in a many to one relationship on TrackingNumber with the list that you already created. When you want to find the partial payments made on a specific traking number, just filter the new list based on this tracking number. Also if you want to find out the remaining amount to be paid just sum all the data from new list and compare it with the total amount from your list.

Hope it helps !
Ramole Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @gabibalaban

I am lost sorry can you tell me a bit more how can i do it an example if you ok with please

Thanks
Verified answer

v-bofeng-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Ramole :

As far as @gabibalaban mentioned ,I suggest you create an associated list to store the payment information.I'v made a test for your reference:

1\data source

My List

Details(associated list)

I can find teh corresponding payment information from the Details list through TrackingNumber.

2\make a simple payment system

a)Add a gallery(Gallery3) and set it's items property to

'My List'

b)Add a textinput control(TextInput1)

c)Add a button and set it's OnSelelct propery to

UpdateIf('My List',ID=Gallery3.Selected.ID,{FreightCharges:Gallery3.Selected.FreightCharges-Value(TextInput1.Text)});/*Update FreightCharges in My List (minus paid items)*/ Patch(Details,Defaults(Details),{Title:Now(),TrackingNumber:Gallery3.Selected.TrackingNumber,payment:Value(TextInput1.Text),Payer:User().FullName}) /*Save this payment information as a record in the Detail list*/

3\Add a table control and set it's items property to:

AddColumns( GroupBy( Filter( Details, TrackingNumber = Gallery3.Selected.TrackingNumber ), "Payer", "NewGroup" ), "Number of payments", CountRows(NewGroup), "Amout", Sum( NewGroup, payment ) )

Best Regards,

Bof
Ramole Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-bofeng-msft

Thank you for you super explaining to details, can you tell me is it possible
1- Instead stamping the FreightCharges 0.00 to blank when full payment is made ?
2- Is it possible whenever i register payment it adds on the same Gallery3 and stamps payment as shown on the screenshot
3- lastly i have a choice field called Status = Open/Closed can i auto close whenever payment equals to 0

Thank you for you support as always.
v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Ramole :

1\You can use Blank() to replace 0.00.For example:

UpdateIf('My list',FreightCharges =0,{FreightCharges:Blank()})

As a prerequisite,you need to turn on 'Formula-level error management'

2\Yes.You chan add a sub-gallery into the gallery and set it's items property to:

AddColumns( GroupBy( Filter( Details, TrackingNumber = ThisItem.TrackingNumber ), "Payer", "NewGroup" ), "Number of payments", CountRows(NewGroup), "Amout", Sum( NewGroup, payment ) )

3\try this formula

UpdateIf('My list',FreightCharges =0,{FreightCharges:Blank(),Status:{Value:"Closed"}})

Best Regards,

Bof
Ramole Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-bofeng-msft

So far so good i am on the last stage just wanted to know how can i get the total original amount to show on the list gallery main example when payment is made full we normal register blank then main 'My List' shows 0 but i wanted like lookup or filter details list to know the original amount?

Thanks
v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Ramole :

I suggest you add one more column for this list to store the original amount.

Best Regards,

Bof
Ramole Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-bofeng-msft

Whats the formula i am not sure its lookup or filter from details list while i am the main list if i want to get the total payment for selected record ?
v-bofeng-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Ramole :

In the solution I provided before, related formulas are already included, please check:

AddColumns(
    GroupBy(
        Filter(
            Details,
            TrackingNumber = ThisItem.TrackingNumber
        ),
        "Payer",
        "NewGroup"
    ),
    "Number of payments",
    CountRows(NewGroup),
    "Amout",
    Sum(
        NewGroup,
        payment
    )
)

Best Regards,

Bof