Multiple Payments dont understand

[imoree]

Hello Everyone;

I am stuck right now. I am trying to design a payment solution, but really dont know how to perform a multi payment

scenario which is what i need.

EG>

Customer pays $50 on a $199 invoice.

Great so far if they only are making 1 payment.

I am trying to create another payment, but it is not carrying over the balance in same table.

My relationship is 1 order => many payments

OrderID = orderID_fk

Perhaps i should just write a script that takes in the amount and then just updates the record? only thing is , i would really like to keep track of

Payment date, payment AMT and then apply to the Order #

Any Ideas are welcome.

thanks again,

-i

[bcooney]

see this thread

[comment]

I am trying to create another payment, but it is not carrying over the balance in same table.

Not sure what you mean by that. The balance can be calculated in the Orders table by subtracting Sum ( Payments::Amount ) from the total.

---

This is assuming each payment applies to one order only - if not, follow the link in Barbara's post above.

[bcooney]

Imoree, Orders may not be the same as Invoices. Payments are more typically related to either the "Billed To (as suggested in the thread)" or the Invoice.

[imoree]

In my case, i call my invoice and order. Will read the thread.

[imoree]

After reading, still not sure. Seems perhaps i need a join table between orders and payments in order to show for example

3 payments made to same order #

eg. InvoiceTotal = $200

Payment = $50

Balance = $150

Payment Date = 12/12/2011

2nd payment

InvoiceTotal = $150 ( now) _ Dont know how to get this to remain

payment = $75

payment Date = 12/23/2011

Balance = $75

3rd payment

InvoiceTotal $75 (now )

payment = $75

Balance = $0.00 ( conditional format changes color )

$$invoiceStatus = "PAID" is set.

Does this make sense now.

I think i may need the join table

@ORDERS ---- > Order»payments < ------ @PAYMENTS

thanks,

-ian

[bcooney]

Yes, I suppose a join will allow Allocations of one Payment across several Orders. The Invoice balance is just the math, InvTotal - sum (allocations).

[comment]

If you want to calculate the running balance in the Payments table, you will need a summary field sTotalAmount (total of Payments::Amount, running, with restart when sorted by Payments::OrderID) and a calculation field =

Orders::Amount - sTotalAmount

Records must be sorted by Payments::OrderID for the running balance to show correctly.

If each payment is allocated fully to a single order, then you do NOT a join table (said that already, I think).

[imoree]

Who thought it would be this hard. brain wrecker this one is.

-i

& i know the issues of returned checks, payments on more than 1 invoice,etc. 1 thing at a time i guess..

[imoree]

If you want to calculate the running balance in the Payments table, you will need a summary field sTotalAmount (total of Payments::Amount, running, with restart when sorted by Payments::OrderID) and a calculation field =

Orders::Amount - sTotalAmount

so what you are saying here is i need to create a NEW summary field in the payments table then , what do i put on the Orders Layout? which fields from the payments table or not important!

Records must be sorted by Payments::OrderID for the running balance to show correctly.

If each payment is allocated fully to a single order, then you do NOT a join table (said that already, I think). I thought so but thanks for reiterating, just am lost major right now. Brain not working fully on this , but i'll get there thanks to you and BCooney!!

[comment]

so what you are saying here is i need to create a NEW summary field in the payments table

Probably. I don't know what OLD summary fields you have in your Payments table.

then , what do i put on the Orders Layout?

If you want the current balance to appear on the Orders layout, you can either calculate it in the Orders table (post #3 above) or show the calculation field from the last record in Payments on the layout (in a portal).

Edited December 27, 2011 by comment

[imoree]

Payments_CRAZY.fp7.zip

i had to do a sample file. Please TELL me what i am doing wrong here.

I want to have an empty field to enter in a new payment on the invoice. Again , pardon the appearance, i slapped this

file together to test the method before messing up what i currently have.

I will appreciate any feedback.

-ian

[comment]

See if this helps.

Payments_SANE.zip

[imoree]

Is there any way to rid myself of this portal or when i script the payment method i can just send them to a layout with

either the list view or a portal with same fields. ..

I thought you can only use a summary for extended Price, You have simplified my life and i thank you so much. THis was about to break my skull!!

You Rock Comment!!! or Michael..

-i

[imoree]

See if this helps.

IT DID!!

it is always easier when someone else does it. Thanks again.