2 Replies Latest reply on Jun 18, 2010 1:14 AM by mr.b.

    How to check for unique related records in a found set



      How to check for unique related records in a found set


      Hi All,


      I have a database that is doing bookings, invoicing and payments for my wife's medical practice.


      When a booking is required to transfer to an invoice, it's "Invoice" field is marked with a "yes" via a checkbox. I then perform a find for all bookings with a "yes" that don't have an invoiceID_fk associated with it and the results are what is used for data on the invoice.


      This all works perfectly but it is possible to invoice two different patients on the same invoice, which is occasionally necessary but generally not so I would like to add an If statement to the found set to check that the related records have a unique patientID_fk, ie I can have multiple bookings from the same patient but if there is bookings from a second or greater patient then it will trigger a warning.


      Can anyone help with how to approach this. I'm sure it's a a Count function but just not sure how to use it.


      Many Thanx in advance for any help.


      Mark B


        • 1. Re: How to check for unique related records in a found set

          To simplify things:


          What about using the same Numerical Field (i'm assuming it's an AutoEntered FMP Serial No.?) and Layout for both Bookings AND Invoices, with the options set for the Invoice/Booking Key Field to be validated Always and to contain a Unique Value?  Then all that would need changed is the Status, which you are currently doing.


          It sounds like most of the same information used in a Booking is also used in an Invoice, correct? 


          Depending on what version of FMP you are using, you can create Script Triggers to run upon the status being changed to Invoice, etc.


          Regardless, Bookings remain bookings and only become an Invoice when Invoiced and each Invoice will always have a unique Invoice Number.


          Just curious.

          • 2. Re: How to check for unique related records in a found set

            Hi rdowler. thanx for your suggestion,


            Unfortunately I'm not sure if that would works as I quite often have bookings from different patients, multiple bookings from the same patient on an invoice and also have different payees paying invoices in a variety of ways, i.e. for their children, insurance companies etc, so it seems best to keep it all separate and to be fair it all works perfectly at the moment so I'm reluctant to change it now.


            It's just that we have a new member of staff working for us and she's managing to find ways of making mistakes that I never thought possible! Hence as these happen I'm trying to add little safety nets to avoid them becoming a regular occurrence!


            Like i mentioned i already have a found set of bookings that need to be invoiced, each booking has a patientID  and all i need to know is if all of the patientID's in the found set are the same or not. I'm sure it's a very easy calculation, just not sure how to do it as I've never needed to before!