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

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

    PaulPijnenborg

      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
      A11-1-15200
      23-1-1550
      37-1-15300
      48-1-15100
      B11-1-1550
      23-1-15200
      37-1-15100
      48-1-15300

       

       

      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)
          ErikWegweiser

          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)
            PaulPijnenborg

            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

             

            Thanks

            Paul