Checking found set for multiple ID's

[Mark Ballard]

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, i.e. 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!

 

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

 

[comment]

I don't see why you need to check this, instead of sorting the found records by PatientID and creating an invoice for each group.

[Mark Ballard]

Hi Comment,

It is simply to point out to the user that they are about to invoice for two or more different patients on the same invoice and verify that that is exactly what they want to do.

Generally that's not what's required, 99% of the time the invoice will be for one patient even if it is multiple bookings.

I have a new member of staff working for us that is creating issues that I have never envisaged!

So far the whole system is working perfectly, I'm just trying to put in a few checks to avoid me having to go back and correct the mistakes!

[comment]

Sorry, I still don't follow. If you have found all uninvoiced bookings, there's a very good chance they belong to several patients, I'd think. But it shouldn't matter to the user one way or another, if the creation of invoices is scripted.

If they are invoicing manually, you should present the user with a report of the found bookings, summarized by PatientID, so that they have a clear picture of what needs to be done.

[Mark Ballard]

I've attached a screen shot of part of the patient screen to hopefully explain a bit better.

Generally an invoice is created, by the consultant, at the end of treating a patient. Hence it is normally only 1 patient being invoiced, possibly for several previous treatments.

You simply click in the "Inv" square and then on one of the buttons above, Insurance Invoice, Invoice or Receipt depending on how the patient is paying.

Clicking on one of these buttons triggers a script to create an invoice with a script parameter to decide the type of invoice/Receipt.

It is possible to be looking at a previous patients records and select the "Inv" for one of their bookings and then change to another patient and click on "Inv" for one of their bookings and hence end up invoicing for two different patients on one invoice. Now this is sometimes desireable but not normally.

They do get presented with the information before the invoice is created but from past experience, they don't actually look at it, let alone read what is there and just click the ok button. Hence why I am trying to insert a warning for them just as an alert for them to verify that they are actually meant to be invoicing two or more patients on one invoice.

I Hope that helps explain what I'm trying to do?

[TheTominator]

all i need to know is if all of the patientID's in the found set are the same or not.

 

There are a handful of ways to achieve this depending on your constraints (minimize additional fields, minimize scripting, minimize new relationships/table occurrences, etc).

One way is to add a summary field

sSumPatientIDs = Sum of patientID

Then your If statement calc could be

If GetSummary(sSumPatientIDs; sSumPatientIDs) = patientID * Get(FoundCount) Then ...

If all patientIDs are the same then the sum over the found set should equal any patientID times the found count. If the amounts are different, you know you have at least one patientID that is different.

[TheTominator]

If you want to do this purely with scripts and no additional fields, you could do something like this

create a new New Window (work in new window to preserve original found set)

Set variable $thePatientID = patientID

Enter Find Mode

Set Field patientID=$thePatientID

Omit

Constrain Found Set

If (Get(Found Count) > 0) you have distinct patientIDs in your set.

Close Window

[comment]

You can tell easily these bookings are for several patients by sorting them by PatientID and showing a sub-summary part.

Alternatively, you could sort them by PatientID and check if the first and last record have the same PatientID. Or check by calculation (again, after sorting) =

GetNthRecord ( PatientID ; 1 ) = GetNthRecord ( PatientID ; Get (FoundCount) )

However, I still think you are putting your users (and possibly yourself) to a lot of unnecessary work. I can understand a human needs to mark the bookings to be invoiced - but once that is done, the solution should know who to invoice for what.

If all patientIDs are the same then the sum over the found set should equal any patientID times the found count.

I am not at all convinced the opposite is equally true.

[bruceR]

When multiples are done right - there *should* be two patients for this invoice - how exactly is that stored? Invoice::ContactID holds two values? There is a join table?

[Mark Ballard]

Hi again Comment, The Tominator, and Bruce.

Thanx all for your suggestions, I decided to go with Comments as it seemed so concise and it works beautifully.

As for your concerns Comment, I have tried to make the solution as easy to operate as possible. Before starting my solution, I tried several commercial varieties that were simply to complex to use for one or two people, especially when it comes to bookings and diaries. The beauty of what I have now is that it is a doddle to use and thanx to SeedCode's calendar module that I've integrated it display everything in a very easy drag and drop style manner.

The trouble with the invoicing is only that we have so many different possibilities, Single/Multiple Patient, Single/Multiple bookings, Receipt/Invoice/Insurance that trying to get this into a simple two click operation took some time. However like i say it has been working very well for a few years now, it's just that with a new operator using the solution they find new ways of doing things that you never dreamed of and I'm trying to put little warnings in to avoid these very occasional mistakes while not disturbing how easy the system is to use.

Thanx again for your help, as I'm a sound engineer for a living I only get to do this when there is a problem or something needs updating so my knowledge, while growing, is fairly limited! I appreciate your patience in sorting me out a resolution.

[TheTominator]

Or check by calculation (again, after sorting) =

GetNthRecord ( PatientID ; 1 ) = GetNthRecord ( PatientID ; Get (FoundCount) )

I am not at all convinced the opposite is equally true.

Ah. Good catch. You are right to be unconvinced.

My solution is quick and dirty and works most of the time, but fails for specific cases (which I accidentally overlooked the first time around).

Your solution is much cleaner and reliable. Well done.