11 Replies Latest reply on Mar 28, 2013 5:22 PM by ThomasStout8600

    Displaying Data from 2 Tables

    ThomasStout8600

      Title

      Displaying Data from 2 Tables

      Post

            

           Hello,

           I have separate tables for Line Items and Payments.  When I record one line item and one payment, I can print a receipt that shows the one line. 

           For example:

           Service A: 90.00   Payment: 90.00

           However, when I have 2 or 3 line items, I can only display the payment for the total amount.  I can't allocate part of the full payment to each line item. 

           For example:

           Service A: 90.00

           Product A: 30.00

           Product B: 40.00

           Payment: 160.00

           And what I'd like to see is:

            

           Service A: 90.00   Payment: 90.00

           Product A: 30.00   Payment: 30.00

           Product B: 40.00   Payment: 40.00

                                    Total Payment: 160.00

            

            

            

            

           How do I get the version I want?

           Thanks for any help,

           Thomas

            

           PS I'm running FMPro Advanced v10.3 on a Mac - 10.8.2

            

        • 1. Re: Displaying Data from 2 Tables
          philmodjunk

               What tables and relationships do you have? Assigning payments lineitem by line item seems very unusual. Splitting a payment up over more than one invoice--each of which links to multiple line items, is not at all unusual and can be done with a Join table in a many to many relationship between a payments and invoices.

               Which causes me to wonder if perhaps you don't actually need to do this to get the results that you need.

               That said, what you show could be done by adding a number field to your line items table for recording a partial payment. Depending on how you want to divide up a payment when the payment does not pay off the invoice in full will determine how you might set this up--whether you might use a calculation field or a script that loops through your line items records and assigns payment portions to them.

          • 2. Re: Displaying Data from 2 Tables
            ThomasStout8600

                 This started as a flat file DB and I've never combined the files.  Therefore, I have a file/table for Sessions, Line Items and Payments.  

                 I do need to figure out what to do about this problem because it came up again today.  I bill insurance via CPT Codes in 15 minute increments.  So there are 4 codes per hour.  I mistakenly entered a 1 under the column "Days/Units" and the insurance company sent me payment for 1 unit.  In recording this partial payment, I'd like to assign it to one of the codes and have the new total reflected in the result.

                 Can you give me any suggestions?

                 Thanks

                 Thomas

                  

            • 3. Re: Displaying Data from 2 Tables
              philmodjunk

                   This started as a flat file DB and I've never combined the files.  Therefore, I have a file/table for Sessions, Line Items and Payments.

                   Technically, that's not a "flat file" database. It's just a multiple file relational database typical of Filemaker DBs prior to the release of FileMaker 7.

                   I still do not see why a payment must be split up over specific line items.

              • 4. Re: Displaying Data from 2 Tables
                ThomasStout8600

                     I'm not sure how to explain it any better but I know what I'd like to be able to do.  Not sure why you can't see it so I'll try again.

                     I do a session, it has 4 line items:

                     1) 97124 = $35.00

                     2) 97124 = $35.00

                     3) 97124 = $35.00

                     4) 97124 = $35.00

                     Total: $140

                     I get a payment of $35.00 for #1 but #2, 3 & 4 are unpaid.

                     In the Sessions & Payments file, I record this as a partial payment so the overall balance is now $105.

                     However, in my Line Items file, if I search for Unpaid Balances, the result is $140.  And if I search for Paid Balances it will return $140.  Both results apply for this one session.  That's not correct but I don't know how to fix it.

                     Does this help?

                     Thomas

                      

                • 5. Re: Displaying Data from 2 Tables
                  philmodjunk

                       It tells me what but not why.

                       Generally speaking an invoice is either paid or unpaid. For most businesses, individual line items are not normally checked off as paid or unpaid. Thus, most businesses search for unpaid invoices, not unpaid line items.

                       Not understanding why this is required leaves me unsure as to how to proceed with a suggested solution since most invoicing systems manage payments with a related payments thable that links to invoices. What you describe would require linking payments to the line items.

                       LineItems---<LineItem_Payment>-------Payments

                       LineItems::__pkLineItemID = LineItem_Payment::_fkLineItemID
                       Payments::__pkPaymentId = LineItem_Payment::_fkPaymentID

                       This allows for a single payment to pay off multiple LIneItems or, in the case of partial payments, for more than one payment to pay off an individual lineitem. This last may be very unlikely for your business, but if nothing else, you may need that option to deal with cases where a partial payment is made in error. A number field in Payments would record the total amount. A number field in LineItem_Payment reocrds the portion of that total payment that is applied to that specific line item.

                  • 6. Re: Displaying Data from 2 Tables
                    ThomasStout8600

                         I agree that "Generally speaking an invoice is either paid or unpaid" and I never had any problem until I ran across an instance where an invoice was partially paid.

                         I'll try to set up a Join Table between LineItems and Payments.

                         Thanks,

                         Thomas

                          

                    • 7. Re: Displaying Data from 2 Tables
                      philmodjunk

                           Yet partially paid invoices are very common. What seems unusual is trying to check off a specific portion of the items listed in the invoice as paid or unpaid.

                           I suspect this may be due to invoicing clients (patients?) for services rendered on different dates and times in one invoice while insurance compensation is received on a fee for service basis.

                      • 8. Re: Displaying Data from 2 Tables
                        ThomasStout8600

                             When the insurance company reimburses me, they list it line item by line item.

                              

                        • 9. Re: Displaying Data from 2 Tables
                          ThomasStout8600

                                

                               I have another question that I think is related to the last one so I'll post it here.

                               In my sessions table, there's a field for session number.  On a client's first session, that number is 1.  On their 2nd, it's 2 and so forth.

                               However, each session can have many line items.  Therefore, if I add a Sessions::No to the Sessions Line Item table, it shows a #2 for all line items belonging to that 2nd session.  I'd prefer to have it show only the number 2 for the first line item.  Is that possible?  My guess is that I should create a calc field in Sessions Line Items that shows the number for only the first iteration?  What would that calc be?

                               thanks,

                               Thomas

                          • 10. Re: Displaying Data from 2 Tables
                            philmodjunk

                                 Don't think that you really need that field at all. If you have this relationship:

                                 Sessions::__pkSessionID = SessionLineItems::_fkSessionID

                                 You can add the sessions line number from the sessions table to layouts or portals that list your line items. In some cases--especially your portals, this requires careful attention to your relationships and maybe the addition of a new occurrence of Sessions to your relationships graph, but it will work. Generally speaking, except for the _fk field, you want to avoid duplicating data from Sessions in your SessionLineItems table.

                                 That said, in what kind of context do you want to see that session number only for the first line item? What sort of layout design are you working from and on what table (Tutorial: What are Table Occurrences?) is that layout based?

                                 In many cases, a conditional format can be set up that hides the session number except for the first lineitem for a given session.

                            • 11. Re: Displaying Data from 2 Tables
                              ThomasStout8600

                                   Hello, 

                                   I'm back trying to figure out the Session Number question.  Sorry, but I don't know what you mean by: "Sessions::__pkSessionID = SessionLineItems::_fkSessionID"

                              Here's what I've done in the past, not that it's correct.

                              I have a field that calculates the number of hours that I worked with each client.  So for an 60 minute session, it's 1, for a 90 minute session, it's 1.5.  That works when I'm totalling the number of hours.

                              On a report, I want to see the hours and the number of sessions.  An hour session and a 90 minute session are 2 sessions.  So I created a Summary field for Number of Sessions = Count of Hours per Session.  This works for the above example and returns a value of 2 sessions.

                                   The problem comes when there's mulitple line items for each session.  Let's say an hour session is divided into 4 - 15 minute increments but it's still one session.  When I look at my Summary Field, it returns the number 4 instead of 1 because it's counting each line.

                                   How do I remedy this?

                                   Thanks for any help and sorry I'm so slow with all of this,

                                   Thomas