2 Replies Latest reply on Dec 4, 2015 8:07 AM by PaulPijnenborg

    Help wanted with getting records sorted for report (see explanation)


      Hello all,


      2 questions, as I am totally lost and out of solutions. The logic is simple but....


      Question 1:

      I have a invoice record where 2 fields {doctor1 and doctor2} related by the same original table in relationships (doctor1 and copied docter2).

      So each invoice has 2 different doctors where 1 is the leading and 2 is secondary. Both have different values to declare and each their own invoice.


      Now the problem: I want to make a list which is sorted by the name of the doctor AND get the values related to him/her:


      doctor invoice nrdatevalue 1value 2



      Question 2:


      It is more or less the same as question 1.


      Next to invoices they can declare costs to insurance companies. So i copied /reconfigurated the invoice tables / relationships to (let us call it ) BOLETIM.

      There is no relationship between invoices and boletim. The original doctor table is copied in relationships (doctor 3 and doctor 4).

      So we use only one table to get all fields filled.


      Now how can I combine both Invoices and Boletim in one list like above?



      I hope it makes any sense and would be really grateful with your help solving this.


      Thanks Paul

        • 1. Re: Help wanted with getting records sorted for report (see explanation)

          Hi, Paul:


          I'd suggest reassessing the structure of the database. For instance, it sounds like you have a scenario where a single patient visit might involve two (or more) doctors, and you want that visit to generate separate invoices for each doctor.


          Perhaps what you need is a VISIT table (the patient visit) with related records (in a portal) for each service or expense incurred during the visit. Each line item in the visit would indicate which of the two (or more) doctors that expense is attributed.


          Then, from the line item table, you can generate invoices for the doctor, because each line belongs to one. You could do this with or without an INVOICE table. With an invoice table, you would create an invoice record belonging to one doctor. Each line item from that visit (or perhaps line items from multiple visits, but for the same doctor) would also belong to one invoice.


          Does that help?

          • 2. Re: Help wanted with getting records sorted for report (see explanation)

            Hallo Erik,


            Thanks for the answer.


            I understand your vision.

            But here when a patient is having a treatment there are 2 doctors present. Doctor 1 gets 100% value and doctor 2 gets 30% of the same treatment(s).


            What i do is this; i make the invoice where fields calculate each value of the doctor. to print the invoice for each doctor i made 2 layouts using the data needed. The patient receives invoice 1 and invoice 1A (where "A" is added as text in the layout).


            I don't see your solution working here or i am to blind to see it