1 2 3 Previous Next 75 Replies Latest reply on Jun 15, 2012 10:31 AM by philmodjunk

    Look up from two fields from different records and payment solution issues

      Title

      Look up from two fields from different records and payment solution issues

      Post

      Hi

       

      I am having trouble determing what records the layout will show by using the find records script. I know it runs each find/omit in the order it is set, but It doesnt seem to do as I ask. Any idea where I may be going wrong?. I want the find to work like the sort works but I cant get it right as it either shows all records from other tables or doesnt omit the records based on what is in a certain field. Do I need to add "" around text when doing a search via the script?

        • 1. Re: Look up from two fields from different records and payment solution issues
          philmodjunk

          I suggest taking this on one issue at a time. I need more info.

          You list the tables you have, but don't indicate how hey are related.

          How is Invoice details linked to these tables?

          Invoices, Orders, Customer, Credits?

          You've put fields from each of these tables on a Payments label that is based on invoice details. How do you plan to use this layout to manage customer payments? Shouldn't payments be recorded in a payments table?

          Here's a typical structure for what you are doing:

          Customers----<Orders-----Invoices-----<LineItems>------ProductsAndServices

          Customers----<Payments---<Payment_Invoice>----PaidInvoices

          ----< means one to many and PaidInvoices would be a second occurrence of Invoices. Depending on how you manage credits, credits could be records in the Payments table or a separate table that would link either to Invoices or Payment_Invoice as well as to Customers.

          • 2. Re: Look up from two fields from different records and payment solution issues

            Hi Phil

            Yes, it was a bt vague! I will get back to you after the weekend if you dont mind as a little busy. If get a minute I shall do it but otherwise have a good one..
            Will 

            • 3. Re: Look up from two fields from different records and payment solution issues

              Hi Phil

              I have attached an image of the relationship diagram. So you are suggesing that I need to set up a payments table and link this to the payments layout? Shoudl I alter the credit and invoices layouts too?

              MAny thanks

              Will

              • 4. Re: Look up from two fields from different records and payment solution issues
                philmodjunk

                It depends on how the business chooses to handle payments. If they only take a single payment in full for a single invoice, then you can record payments in invoice details. If it's possible for a customer to make one payment to pay for more than one invoice or to make partial payments against an invoice, then you need one or more more additional tables to track payments and to link them to the correct invoices to show which are paid and unpaid.

                Please answer these questions:

                Can a customer make a partial payment? (I'll pay $20 on credit card 1 and $40 on credit card 2. I'll pay $100 now and $300 dollars next month...)

                Can a customer pay off more than one invoice with a single payment? (This payment of $600 pays off invoices 1 and 2. Apply the balance to invice 3...)

                • 5. Re: Look up from two fields from different records and payment solution issues

                  Hi Phil

                  Firstly, Every client (95%) pays a deposit first. This can be done at the point of receiving a quote, or placing an order. The balance paid (less the VAT) is taken off the invoice total at the end, when the final invoice is issued.
                  There does need to be an option if the client pays in one or more parts and via different methods - some may have paid deposit by credit card and the balance by BACS. But the client can also pay for three invoices in one go, if they wish...

                  I can send you a copy of my DB if that helps...?

                  Thank you 

                  • 6. Re: Look up from two fields from different records and payment solution issues
                    philmodjunk

                    Ok, that's not at all unusual and describes a many to many relationship between your invoices and your payments.

                    I'd link them like this into your existing tables:

                    InvoiceDetails-----<Invoice_Payment>-----Payments>-----PayCustomers    (---< is "one to many", PayCustomers is an occurrence of Customers)

                    InvoiceDetails::_kp_invoice_id = Invoice_Payment::_kf_Invoice_id
                    Payments::_kp_payment_id = Invoice_Payment::_kf_Payment_id
                    PayCustomers::pk_customers_id = Invoice_Payment::_kf_Customer_id

                    You can set up a layout to Payments where you create a new record each time a payment is received. You select the customer ID and then use a portal to Invoice_Payment to assign the payment to one or more invoices. A number field in Invoice_Payment records the portion of the payment received that is applied to a specified invoice. It's possible to set up a filtered portal of unpaid invoices for the selected client so that you can click an invoice to create a record in Invoice_Payment to apply a payment portion to it. It's also possible to use a script that takes the payment amount entered and loops through the unpaid invoices for that customer paying off the oldest unpaid invoice first and then moving on to the next unpaid invoice until either all invoices are paid or the entire payment has been applied to one or more outstanding invoices.

                    It's also possible, with scripting, to set this up so that the original deposit is logged as a payment in the Payments table, with a corresponding join record in Invoice_Payments simply by filling in a deposit amount on the invoice details layout.

                    • 7. Re: Look up from two fields from different records and payment solution issues

                      Thank you Phil

                      I shall read all that and have a go at altering the various parts...

                      • 8. Re: Look up from two fields from different records and payment solution issues

                        Should I add a new table called "invoice_Payment" and record within called payment id? Or is this the same as my existing table::field Invoices::Invoice ID?

                        What fields should this table and pay customers contain? Just the ID fields you mentioned?

                        Do these new fields need to be set as serial numbers or anything?

                        Once I have created these tables and fields, I should just join them in the way you point out? (what about adding new records or deleting records?)

                        I think this is supposed to be the easy bit! 

                        thank you

                        • 9. Re: Look up from two fields from different records and payment solution issues
                          philmodjunk

                          invoice_payment is a new table. It serves as a join table between invoice details and payments so that you can both link a payment to an invoice and also to "divide up" a payment over several invoices.

                          I've already described the three fields you need in this table, The two ID fields, and number field where you record the portion of a given payment that applies to the invoice that's linked to it.

                          The fields with kf are foreign keys, they get their values from the primary keys (kp) that should be defined as auto-entered serial numbers. The only new serial number field is Payments::_kp_payment_id.

                          PayCustomers is a new table occurrence of the Customers table that you already have. You select customers in Manage | Database | Relationships and then click the duplicate button (two green plus signs) to create it. You can then double click the new occurrence to open a dialog where you can rename it. This does not duplicate the actual table, it just creates a new reference to your existing customers table. I've included it here as it will enable you to add fields from the customer table to a payment layout so that a customer name, billing address, etc can appear once you have selected a customer ID for the payment record.

                          This is a many to many relationship like you already have with Invoice details---<Lines>----Products. Lines is the Join table that links a product to the invoice where such a product is sold. Invoice_Payment serves as much the same link and a portal to Invoice_Payments (as I have already described) can be used to document the details of a given payment by linking partial ammounts of the total payment to multiple invoices. (And a portal to Invoice_Payment on your invoice details layout can list all payments made to that invoice.)

                          • 10. Re: Look up from two fields from different records and payment solution issues

                            Thank you very much for clearing that up for me....I shall tackle it all now and see how I get on. 

                            • 11. Re: Look up from two fields from different records and payment solution issues

                              Hi Phil

                              I have just created the tables and fields plus relationshiops. I have attached the new version - is this how you envisaged it?

                               

                              You mentioned the relationships should be:InvoiceDetails-----<Invoice_Payment>-----Payments>-----PayCustomers   but this is different to what you also say beneath that - 

                              InvoiceDetails::_kp_invoice_id = Invoice_Payment::_kf_Invoice_id
                              Payments::_kp_payment_id = Invoice_Payment::_kf_Payment_id
                              PayCustomers::pk_customers_id = Invoice_Payment::_kf_Customer_id

                              or have I misunderstood?

                              How do you suggest I design the layout for the new Payments layout? OR should I just reattach the new payments table to my existing payment layout and jjust change the link from invoice details to Payments..? Before, I had a find to show all records with an invoice number so I could then apply amounts to the invoices::paid field and this would alter the Invoices::amountowed field. Shoudl this approach still work? If not how t=do you suggest I go about it as I havent tsaken into consideration how to add different amounts as mentioned earlier, or indeed credits....

                              Thank you

                              Will

                              • 12. Re: Look up from two fields from different records and payment solution issues
                                philmodjunk

                                It took me a bit to see the discrepancy.

                                You don't need to link customer records directly to the join table. They should link to the Payments table:

                                Payments::_kf_Customer_id = PayCustomer::pk_Customers_id

                                A Payments layout will need to provide you with the ability to log all payments info (check/credit card info, date, amount of payment, customer ID, etc.) In addition, you'll need to provide some means of interacting with a portal to Invoice_Payment so you can work with cases where a payment must be applied to multiple invoices. You may want to have more than one layout for payments. One could be set up as a list or table view of all payments so that you can see what payments have been made and compute daily, monthly, quarterly, etc totals for payments received. Another might be a form view where you use a portal to Invoice_Payment to fill in the details. You might even want a second portal that lists all unpaid invoices for the customer selected for that payment record so that you can review and select invoices to which to apply the payment.

                                There are a number of scripted approaches for streamlining the management of your Invoice_Payment records so that you don't have to manually enter data when you don't need to.

                                • 13. Re: Look up from two fields from different records and payment solution issues

                                  Hi Phil

                                  Attached is updated relationship chart. Hope it is now correct. I am now in a massive muddle...

                                  The way the databese works is:

                                  From the customers layout and associated 'customers' table, the suer can raise a new quote or a new order. Each has their own reference number. (quote number for quote, or job number for order). If a quote is converted to an order, then it is also given a job number. A depost can be paid at the point of quote or order. And of course an invoice is raised from an order. 

                                  I have several tables, The main two are customers (contains customer details) and Invoiuce details. Invoice details contains all the shipping details, line totals, vat ewtc etc. The order table quote table and invoice table only contain the job/quote/invoice numbers, dates etc (as well as the pks and fks etc to link them to the invoice table).

                                  Am I supposed to have kept the totals of the orders/quotes/invoices into the respective tables or is it ok to have themin the invoice details..?

                                  IS there any chance I can send you over a copy of my fm DB for you to qucikly see what i have done as it is quite tricky to outline it all succintly here...

                                   

                                  Many thanks again

                                  • 14. Re: Look up from two fields from different records and payment solution issues
                                    philmodjunk

                                    It looks correct. You'll only be processing payments for those records in Invoice Details that actually produced an invoice (They'll have a matching record in Invoice if they do. Correct?

                                    Here's how it works "under the hood".

                                    A customer sends you a check for $350. You create a single record in payment. Log it as a check, lot the date received and the amount: $350. You also look up and enter the ID for this customer.

                                    You then determine that there are two unpaid invoices. The oldest has an unpaid balance of $250. The second has an unpaid balance of $400. You then create a record in Invoice_Payments that links to the oldest unpaid invoice. You enter $250 into the amount field in this new record. You then create a new record in Invoice_Payment, link it to the second invoice and log the remainder, $100 in the amount field of this new record. The unpaid balance on this invoice will now compute to $300.

                                    The key to all this is to design layouts and scripts to better facilitate this process so that you aren't sitting there with a calculator figuring out what portion of such a payment should be applied to a given unpaid invoice.

                                    Here's one layout tool that can help you get started:

                                    Make a new occurrence of Invoice_Details. (Just like we did with PayCustomers)

                                    Link it to payments like this:

                                    Payments::_kf_customer_id = CustomerInvoices::_kf_customer_id

                                    A portal to CustomerInvoices will list all invoices (and quotes and orders...) for that customer. You can then define a portal filter that limits the records shown to those that are actual, unpaid invoices. You can define a calculation field, cInvoiceStatus, in Invoice details as:

                                    Case ( IsEmpty (Invoices::InvoiceNumber ) ; "not an Invoice" ;
                                              InvoiceTotal > Sum ( invoice_payments::amount ) ; "Unpaid" ;
                                              "Paid" )

                                    You can then use this portal filter:

                                    CustomerInvoices::cInvoiceStatus = "Unpaid"

                                    and you'll only see unpaid invoices in the portal.

                                    A button could then be placed that creates a new record in Invoice_Payments with the appropriate ID numbers so that you can assign all or part of the current payment to it.

                                    1 2 3 Previous Next