Hi Everyone, I posted this Last Year(LOL) and didn't get a response, any suggestions would be appreciated.
This depends a bit on your business model. A few questions need to be answered to determine that:
Can a single payment pay off multiple invoices? (I'd say yes from your first post, but let's be sure)
Can a single payment pay off part of an invoice?
Should the payment be designated to a specific invoice or group of invoices or should the system simply select the oldest unpaid invoice and then keep selecting subsequent unpaid invoices until either there are no more unpaid invoices or the the total owed is > to the payment amount?
Unless your answers to the above questions indicate otherwise, you may need to add a payment portion field to Payment (I'd also rename this table Order_Payment as it's really a join table linking your ledger to one or more order records.) to document what portion of the total payment is being applied to that specific order. (This enables a payment to be applied to partially pay an order.)
Unless you want the user to select the applicable orders, a script can take the customer ID, the payment amount and then generate the needed records in Payment for you.
Can a single payment pay off multiple invoices? Yes
Can a single payment pay off part of an invoice? Yes, We may receive a deposit/partial payment for an order.
Should the payment be designated to a specific invoice? Yes, only to specific invoices.
Are there other fields that need to be created besides of what is mentioned in your reply.
Do I need any more tables...or are we good with what I have?
Which layout would I need to create the portal for the list of invoices for One customer.
Thanks Phil for all your help.
I read your original Relationship graph incorrectly.
Order_Payment and Payment Ledger should be two separate tables. I'd enter the payment amount directly into Payment Ledger and then use either a script and/or a portal to Order_Payment to generate the join table records to link the payment to the relevant orders layout. You can assign a payment or payment portion directly to an unpaid order via a drop down. A conditional value list can be defined that only lists unpaid invoices for the selected customer.
Payment_Order need only have these fields:
_kfPaymentID (To link to the total payment record in the ledger table)
_kf_order_id (to link this portion of the total payment to an order record)
PaymentPortion (Number field, the portion of the total payment to be applied to this order record.)
_kfPaymentID in the ledger table should be _kpPaymentID and should be an auto-entered serial number.
This is just to get you started. There are a number of interface implementation tricks you can use to automate all or part of the process of dividing a single payment between two or more unpaid orders.
Updated the relationship diagram, are there any other relationships that need to be setup.
Don't see this one, but maybe you just left it out by accident?
Order_Payment::kfPaymentID = PaymentLedger::kpPaymentID
Note that PaymentID should be an auto-entered serial number based Primary key in PaymentLedger and a foreign key number field in Order_Payment.
Hi Phil, just updated image. What next?
Not sure why you show an sPaymentAmount field in both Order and PaymentLedger. In most naming conventions, that s at the start of the field name designates a summary field. In order, you don't have a payment field to total up, though you could define a calculation field that uses sum ( Order_Payment::PaymentPortion ) to compute the total payments made on a given order record.
Other than that you should be just about all set, though you'll probably want to enable "allow creation of records via this relationship" for Order_Payments in the relationship linking it to the payment ledger. That will make it easier to add new records to the Order_Payment table from a layout based on PaymentLedger.
Then you'll need to figure out the interface that makes the most sense for you.
You can, for example, put a portal to Order_Payments on a payment ledger layout with a drop down list formatted KfORderID field for assigning it to a specific order record and a PaymentPortion field for designating what portion of the total payment is being assigned to this order. You might define an auto-enter calculation for paymentportion that works like this:
If ( Order::BalanceDue > PaymentLedger::PaymentAmount ; PaymentLedger::PaymentAmount ; Order::BalanceDue )
Order::BalanceDue can be defined as OrderTotal - Sum ( Order_Payment::PaymentPortion )
This basic idea can be made much more sophisticated, but that should at least get you started.
Well this is where Iam at. See new image on orig post.
Layout Based On: Payment Ledger
One Portal Based On: Order_Payment
Nothing displays on the portal.
Portal to what table occurrence from what layout?
I've described a portal to Order_Payment placed on the PaymentLedger layout. You won't see any records in the Portal until you create them. If you've enabled Creation of records in the relationship, this is as simple as entering data in the bottom blank row of your portal.
Here's a demo file of a many to many relationship you might want to examine. It relates contracts to companies, but if you were to rename them Orders to Payment Ledger, you'd have the same situation as what you are trying to create here.
I think I am getting there.
I have a portal from OrderPayment set up on PaymentLedger Layout.
When I create a new record and select a customer I only see just one record for that customer on the first row...it doesn't show the rest of them
I updated a new image on the original post...can you check it out maybe something is not quite right.
Sounds like it is performing as expected. As designed, the rows in your portal will list those invoices to which a portion of your payment will be applied. You'd have to add records with links to additional invoices before you'll see more than one new row after selecting from the drop down in the portal.
Please note, that you can create a conditional value list that only lists unpaid invoices or only unpaid invoices for a selected customer to reduce the number of invoices listed in your drop down.
You could also create a script that adds a record for every unpdaid invoice for a specified customer to your portal and then you'd delete the portal rows of any that might not apply, but you'd need to create a script for that.
"You'd have to add records with links to additional invoices" Can you provide me with an example of this?
"create a script that adds a record for every unpdaid invoice for a specified customer" Also an example of this?