10 Replies Latest reply on May 7, 2012 12:15 PM by willrollo

    Quick relationship question...


      Quick relationship question...


      For some reason, I cannot see any data in the fields from the related table, Customers, when viewing a layout based on Payments...(Enquiry Name etc) I have attached the diagram and am sure it will take just a second for someone to notice what is wrong with it. Are my relationships ok oor do I need to create a new T.O. between Payments and Customers?
      I can see customer fields from the invoice/order/quote tables. Just not Payments...
      Thank you


        • 1. Re: Quick relationship question...

          The relationships look like they will work--provided that you have a related record in Invoices and also in Invoice details.

          You might try this experiment:

          add a field from Invoices and see if it displays the correct data.

          Add a field from Invoice details and see if it displays the correct data.

          If you find a break in the "chain" here, you can then investigate to see why/how that happened.

          • 2. Re: Quick relationship question...

            Thank you Phil

            This payment addition to my DB has been driving me round the bend! Will follow your suggestions and get back to you with how a I get on. Thank you again

            • 3. Re: Quick relationship question...

              Hi Phil

              I have tested out as you suggest. My invoices layout, based on invoices or invoice details (for the purpose of this test_) both work. Ie they display related reocrd info from the customers layout. However, if I base the payments layout on the payments table, then no related record fields work (cutomers or invoices etc). I assuem this means that the payments table is not related properly to the other ones. How do I enable this?

              Thank you

              • 4. Re: Quick relationship question...

                Hi Phil

                The related  fields (from customers or invoices tables) in payments layout, based on Payments table, are not populating or allow entry at all if I click in to them when opening a new record. I believe this is either becuase the links are incorrect of the keys arent synced....Any ideas?

                Do I need a join table for this payments solution> Before you suggested I should, if user ever issues credits, or receives oner payment to cover more than one invoice etc. I am having trouble implementing this with my solution...I did follow your previous comments on my post regarding a payments solution but it never really worked- mainly because of the current problem I am experiencing

                • 5. Re: Quick relationship question...

                  Take a look at the Payments::PaymentFK field (I'd actually name this field InvoiceFK as it matches to the InvoicePK field myself...)

                  What value is entered into it. It sounds like the field is blank or that it stores the wrong value to match to a record in the Invoices table.

                  • 6. Re: Quick relationship question...

                    Hi Phil,

                    Thank you. Yes it is indeed empty. I would have thought I need to set field from INvoice PK in INvoices tabvle? Or am I wrong..? Or should I rmeove Invoice:InvoicesPK and just use the Invoices::invocies FK field?


                    I will alter the naming after this is sorted so there is no confusion when referring to the original ERD...

                    • 7. Re: Quick relationship question...

                      There are a variety of methods for entering a value into this field that matches to an invoice in your Invoices table. Which method you use depends on the design of your layouts and your work flow. If you receive payment at the time of sale such as is typical for a POS system, a portal to payments on an Invoices layout with the "allow creation..." option enabled enters the matching value for you when you log a payment in a portal to payments.

                      If, as I think is the case here, you are getting payments after sending the invoice out to the customer, you have to look up the invoice to which the payment is to be linked. The simplest method (not necessarily the best), is to format this field as a drop down list of InvoicePK values. A conditional value list can limit the values in this list to only those that are unpaid or even only unpaid invoices for a specified customer.

                      Of course, if they've followed directions and returned a portion of the invoice with their payment and it contains the invoice number, you can use that with your value list or a search script to find and enter the correct value into this field.

                      • 8. Re: Quick relationship question...

                        that is basically it ---

                        We normally issue an invoice and then receive a cheque/credit card payment for the total of the invoice(s).

                        The old DB layout was  a list tuype layout of the open invoices for all the clients (no more than 30 normally) and a field in this list to enter the payment amount. The other fields in the list were name, order number , balance, date etc..It is avery small business so no need to be super complicated. 

                        It was quite simple. But now I think I would like to click 'receive payment' directly from the invoice page for that customer, which would take the user to a payment layout that has a portal to open invoices for that client, The user can then click a check box by the invoices in the portal to which the payment amount is applied..What has made this tricky is I am not entirely sure where (if I need them) table occurrences should go for this solution. Do I need them? Or have what I have suffice?  And would you suggest a payments join table between invoices and Payments? I dont resally see how this would ne helpful -- I understand the lines join tbale between products and invoice details. But the products are records with set pricrs which may be altered from time to time (hence the lines table)M but payments are alwsy new records I woudl have thought and need to be recorded as they are recieved...?

                        • 9. Re: Quick relationship question...

                          If you put a button on your original list type layout, it could create a new payment record related to the invoice on the row where you clicked the button. This would be a pretty simple script as well.

                          A payments join table is probably a good idea. With such a table, a customer can send you a single payment, and you can apply it to multiple invoides and they can send you a partial payment and you can then apply multiple payments to the same invoice. The join table enables the many to many relationship required to manage those payment scenarios and a field in the join table also records what portion of the total payment to apply to the selected invoice. (You get a payment for $500 and use $200 to pay off Invoice abc and then apply the balance of $300 to the next outstanding invoice for the same customer...)

                          • 10. Re: Quick relationship question...

                            Ok - so in the join table I need to add an amount field -(ie the amount of the payment). Do I also need one in payments? Which field should I put on the Payment layout? The one from the join or the one formpayments, is there is one..?


                            When I join table to relate them, I take it the foreign key should autofiull from the primary key to which it connects from the adjoining table? Are you suggesting I should apply a value list to the Payments::Payments FK field using data from the original customers::PK customer ID field as a dropdown menu? 

                            Thank you!