It seems such a simple question
No, I am afraid it isn't simple at all. Basically, you want "AC" to match all of these: "AC", "CA", "ABC", "ACB", "BAC", "BCA", etc. (but NOT "A" or "C" or "XAY"). On one hand this is a AND condition, but on the other hand the order doesn't mater. This is then further complicated by having to check against numerous exceptions.
I suggest you start by having a look at these:
Thanks for the tips, Comment. I have already used the technique of Case (Printed; "Nes" ; "No) & "¶"& "Both" in filtering portals, thanks, but I couldn't see how to use a variation of it here.
It seemed to me that it 'might be easy' purely on the unscientific basis that the customer's requirement was very easy to state, sounded like a perfectly reasonable request, with no complicated description required! Also it is the kind of thing my supermarket does all the time - "Check the list of Child items that belong to this Parent receipt and see if there are any of Product A and also Product D, in which case reduce the price of Product E to zero."
Although I would not want to do it, in order to free my log-jam I thought about how I would do it if all the Prescription Lines existed on the Parent record, so I was only jumping one step of relationship. I hoped that I could crack that one and then step from there to the true solution. But to no avail.
It is not that I want 'AC' to match 'ABC', 'BCA', CAB', etc in any combination (I don't think), it is that I can already match 'A' to an 'ABC', 'BCA', 'CAB',, and then I can do the same, for a separate record, with 'C'. I can even bring back to the Child Table that they both matched themselves against ExceptionRecordID xx, so I had at least a possibility of highlighting two matching ExceptionRecordIDs. But I stopped working on trying to highlight that two Child records belonging to the same Parent had both picked up the same ExceptionRecordID, because I realised that my next problem was going to be that the 'A' record had picked up ExceptionRecord xx, but if it had looked further down the table it would also have matched ExceptionRecord yy which would have shown that it should not have been combined with Drug D.
My next line of attack will be to search through the Prescription Lines via a script set to trigger on modifcation of any prescription line, but I suspect it is at best going to be a pyrrhic victory - may work technically but make the table unusable in reality.
Thanks for you help again - at least I'll not waste my time.
It is not that I want 'AC' to match 'ABC', 'BCA', CAB', etc in any combination
How come? All of these are violations of the exception - and since drugs could be prescribed in any order, you may have six patients with the same combination of three drugs, but each in a different permutation.
Matching "A" to "ABC" serves no purpose here that I can see. The match must be of "AC" (not "A¶C") to any combination that includes both A and C, in any permutation.
One possible solution is to (a) sort the prescribed drugs by a known sort order (so that "ABC", "ACB", "BAC", "BCA", etc. all resolve to "ABC"), and (b) generate a multi-line key of all subcombinations:
Thank you, comment, I have huge respect for your responses. I think you are [at least] one step ahead of me here. I usually try and build my solutions around asking "How could I make this such that a human could do it in a very boring, repetitive way?". From that approach I could see that from the Prescription Lines Table I could have a portal for each Prescription Line related to the Exceptions Table. For any Prescription Line for Drug A it would show a portal with rows for Exception Records x, y, and z, because Drug A matched to 3 records of return-delimited lists in that Exception Table's records of unacceptable combinations.
If I found all Prescription Lines for PatientID = P, then a human could (technically) scroll down the list of portal rows for each Prescription Line and spot that the line for Drug A matched to Exception Record ID 123 and also the Prescription Line for Drug C had Exception Record 123 in its portal listing too. Hence, Drug A and Drug C have been prescribed for the same Patient, and appear together on a common Exception List - so "Check them out please".
So the Human Routine would have been:
- Find all Prescription Lines for PatientID=P
- For each Prescription Line write down the complete list of ExceptionRecordIDs that the line matched
- Check if any lines had an ExceptionRecordID in common.
- If so, mark the Prescription Lines with a highlighter
It seemed like I'd cracked the human routine, but I can't get it interpreted into a Filemaker routine without needing to have related fields used again as key fields, etc.
Thanks for your help,
Can we rely on the following assumptions?
1. The exception is always about exactly two drugs;
2. In the Prescriptions table, the combination of PatientID and DrugID is unique (i.e. no patient has two prescriptions for the same drug). Think carefully about this, because there is also a time factor involved here.
I have already covered the highlighting of the same Patient apparently being prescribed the same drug twice, so I could say that the combination of PatientID and DrugID is unique.
However I cannot say the same about the the exception being about two drugs. An example is the dispensing or paracetamol: there is a wide range of drugs containing paracetamol and if the patient has already been prescribed one of them then it must query if any of the others are subsequently prescribed. So the Exceptions Table would (under my stymied thinking) will contain a record with all of the paracetamol-containing drugs:
ExceptionRecordID 01 (ExceptionRecordName = "Paracetamol")
But then Drug B should not be prescribed with two sorts of anti-inflammatories, so it also exists in the middle of another Exception Record:
ExceptionRecordID 02 (ExceptionRecordName = "Anti-inflammatories")
If you mean by the 'time factor' that they were prescribed a paracetamol-containing drug two years ago, and are now being prescribed another, then I am not currently covering that possibility. At present it is just that on their list of 'live' prescription lines they may add another which should be highlighted as a dubious combination.
Well, if it wasn't complicated before...
Suppose a patient is taking two drugs, both containing ingredient X (which in this example we presume to be OK).
However, any drug containing X should not be taken together with ingredient Y (which our patient is NOT taking).
Do you see the problem with the routine you have outlined earlier?
You are moving too fast for my intellectual capacity here, comment. Sorry. I will call in my Get-Out-Of-Jail-Free cards that, a) I am Irish, and b) it is late here.
"Suppose a patient is taking two drugs, both containing ingredient X (which in this example we presume to be OK)"
So the Patient's Prescription Lines have among them Drug A and Drug B, both of which contain a common ingredient, but not one that means there is any reason to highlight their combination. Drug A may well appear on an Exception Record somewhere, and so might Drug B, but they do not appear on the same Exception Record anywhere. So in my Human Checking Routine there are no two Prescription Lines that show the same ExceptionRecordID. Therefore no warning is required.
"However, any drug containing X should not be taken together with ingredient Y (which our patient is NOT taking)."
Drug A does indeed appear on an ExceptionRecord along with Drug F (which contains ingredient Y), and on another ExceptionRecord with Drug G, but PatientID=P is not taking either of those drugs F or G so that doesn't have to be highlighted. Drug B also appears on a separate ExceptionRecord with Drug F, but again the Patient is not taking Drug F so there is no common ExceptionRecordID to be highlighted. As soon as Drug F is also prescribed it will show as matching the ExceptionRecordIDs that are also listed for Drug A and Drug B and will then be a cause for highlight.
I know that one problem with the routine that I outlined earlier is that the various combinations to be cross-checked will present a not-insignificant data-management problem, but at this moment I'm trying to solve the technicality of making it work even once that threshold has been crossed.
Sorry: be gentle with me; I need another hint about the flaw in my 'human' routine.
Ok, slower this time (LOL):
Patient is taking (only) Drug A and Drug B, both of which contain ingredient X,
Ingredient X cannot be taken together with any drug that contains ingredient Y.
Drug C contains ingredient Y.
The exception states that "anything with X [a list of drugs that includes Drug A and Drug B] cannot be prescribed together with Drug C." So both Drug A and Drug B match this exception - but they both match it from the same "side".
I think I'm slowly catching up with you, and I can see why you expected the ExceptionLists to be in pairs and only pairs, because it could be the case that the Exception Lists were:
Exception List 01 "Name: Paracetamol" (where no two drugs may be taken in any combination)
Exception List 02 "Name: Anti-inflammatories" (where no drug may be taken in combination with Drug C.)
As you say, Drug A and Drug B would both unnecessarily match from the same 'side' against Exception List 02.
I was expecting the lists to be correctly describable as "No two drugs on this list may be taken in combination", not the cleverer second definition. I did foresee that this was going to result in a data-management problem, but I hadn't moved on to think about that too much as I couldn't solve even the first problem.
To cover the scenario you were describing I recognised that I would need to have Exception Lists like this:
Exception List 01 "Name: Paracetamol"
Exception List 01 "Name: Anti-inflammatories 01"
Exception List 01 "Name: Anti-inflammatories 02"
...and I could see that to make it manageable (or even possible, maybe) it might be simpler or best to have every 'ExceptionLIst' as pairs. If that was the case I suspect it would be unmanagable in practice, though.
A few years ago I worked with a retail program (written in 4D) where we had BOGOFs and multi-buy discounts. I must go back over that and seek inspiration from how we achieved it then.
Thanks again for your help.
Well, the requirement for pairs was only because of your proposed routine of checking for conflicts.
I would probably make the exception of "no drug in group A may be taken in combination with a drug in group B" look like this:
"A1¶A2¶A3" ; "B1¶B2"
but then each prescribed drug has to "know" which side it is related to and consider only the "other" group.
Anyway you decide to go, it's possible - just not very simple.
A few years ago I worked with a retail program (written in 4D) where we had BOGOFs and multi-buy discounts.
I don't know about 4D, but I can tell you how your supermarket does it: they run the equivalent of a script after each entry. They can afford this because of the speed of SQL and also because of the different philosophy of the application. In Filemaker, you never want your data integrity to depend on a script running succesfully; you want to enter your data and let relationships and caclulations take care of the rest.
Bang goes my next idea, then. I was going to add another relationship between the Prescription List and the Exceptions List, using the DrugID and a global ExceptionslIstID. I was going to set a script to run each time a Prescription Line was amended, to:
Go to the first Prescription Line
Check if it appeared on an Exception List.
If so, set that ExceptionListID to be the global value.
Cycle through each Prescription Line checking for a match using that new relationship, ie: another drug is on the same Exception List.
If so, mark the Prescription Line with the Exception List name (adding it to any existing Names)
Go to the next original ExceptionList match for that Prescription Line, re-set the global ExceptionLIstID and do the same check through every Prescription Line
Go to the next Prescription Line and loop through those checks again.
But I accept your point about not wanting data integrity to rely on a script running successfully, so I'll bin that idea. I have other modules to be getting on with, so I'll come back to this in a few weeks time again.
Thanks for all your help,