One way is to create a self-join relationship to itself. Link by whatever fields that define the uniqueness.
Then OnObjectSave trigger, look if the data is present in that table, which means identical record is already there.
Another way is to add products via script, which lets you check if a given product is already present as a line item; and/or use a method to only display products not yet selected for the current invoice.
(List(), FilterValues(), IsEmpty() et cie. are quite helpful here … )
A scripted approach will also be necessary should you start inventory management w/ stock levels etc.
A third way would be to create a calculation field in the child table that concatenates the fk and the field or fields that you want to enforce uniqueness and set validation to unique on the calculation.
Not saying its a better way just another.
Yep, but if you build it into the field validation... you get that weird dialog box which
A: can't be overruled
B: we have no control over except defining a custom message
C:and is not that flexible.
Of course it's the most secure way getting it down at that level but...
If he wants to validate on only some occasions and not others it can get tricky pretty quick.
I think a scripted approach would be best, you can write different set of rules according to the model.
As I said "Not saying its a better way just another."
I try to present options and let the developer decide if/how they want to use it.
Sure, it was in no way a critique. Just like you said: options.
I would use a join table midway between "Order" and "Product". So if you had Order #1234 for 50 copies of Product #5678, you could insist that the combined value 1234.5678 be unique, and it would only affect that one order.
Thank you all I think I have got some ideas to try out
There are many solutions available, as the other posters outlined. From my point of view, much depends on your interface, on 2 different levels:
- First level: how you handle potential problems. Do you give warnings but allow exceptions, do you prohibit anything not being kosher, do you use, or consider using, windialogs with ok and cancel containing only globals that you postprocess, etc.
- Second level: How does the data get into (specific) fields. Popup, pulldown, free typing, click on a portal with valid choices that go into the field, drag and drop etc.
In a perfect world, the interface has to stay consistent so it's important to know the rules of the game before leaning towards one or another method.
For example, a potential solution could also be the compacting of an order.
say you're on the phone and the client says
- 2 pizza margherita
- 2 pizza al tonno
- 1 frutti di mare
- 3 boscaiola
- 2 house special
- 1 margherita
- 2 tonno
- 3 four seasons
- 2 frutti di mare
- 1 tonno
you ask: Is that all, Sir ?
he answers: add a boscaiola then it's ok.
NOW you press the COMPACT ORDER button, and recalc everything.
Your interaction with the database was not disrupted while taking the order, which is good for speed, and you have what the client wants.
(I bet you're all hungry by now and calling speedy pizza)
Nice , i presume this is what is called the dwindling value list on some threads in other forums
I created simple script that deletes the duplicate record immediately
Sample file is attached ........
Try adding a new project in the portal .... which is already added eg. Project 1 or Project 2
A warning message will popup and It will be deleted immediately .
Sample.fmp12.zip 68.8 K