First off, a table with 300 fields seems oddly non-normalized. If you can post a sample of the table, it may make more sense to structure the table differently.
Secondly, there are two types of validation you can do in FileMaker, field-level validation and script-triggered validation. I would actually recommend the second one to you, as field-level will only take place when saving the record, which may not happen when you move from field to field.
So you would be looking at the onObjectExit script trigger, and a script that evaluates the current field against the previous fields.
A script could look something like:
If [ Get(ActiveLayoutObjectName) = "field1" ]
If [ //field1 validation condition <> 1 ]
Show Custom Dialog [ "ERROR" ; "Field 1 is not valid" ]
Exit Script [ False ]
Else If [ Get(ActiveLayoutObjectName) = "field2" ]
If [ //field2 validation condition <> 1 ]
Show Custom Dialog [ "ERROR" ; "Field 2 is not valid" ]
Exit Script [ False ]
Else If [ etc... ]
This would be lengthy for 300 fields, but would allow you to customize the validation condition of each of the 300 fields, and exit without allowing proceeding through the form.
You could also do pre-validation with an onObjectEnter script step to make sure that previous fields have been completed to specification before allowing a user to activate a field on the form.
Lastly, I would look into virtual value lists set up with the onObjectEnter script step, otherwise your relationship graph will become unusable trying to make so many related/filtered value lists.
thank you for your exhaustive explanation.
I have not the permission of sharing a copy of the two Tables but they look like exactly as follows:
TableA (the primary Table)
Link = 1 (calculated)
Field 300 (text).
TableB (the values Table)
Link = 1 (calculated)
I did think about a trigger as your suggest onObjectExit, but for 300 fields....it is a nightmare.
Do you think it is possible to have the value list (which is the same for all the 300 fields) automatically hide those values already entered ? I was thinking about a dwindling technique (which I have seen at work but never used)
Yes, it's possible, however you still would have to trigger the update of the value list somehow after each field is entered, so your nightmare would continue.
If you could pivot the table so you have 300 records instead of 300 fields, then possibly you could do smarter logic with less work.
my friend explained me some critical details about his solution...at least I can understand why he wants 300 fields.
His solution is about to manage his restaurant reservations and the 300 fields represents the physical disposition of the restaurant tables.
So in his view,
TableA is a unique record showing all tables,
TableB is the value list with numbers of tables (1A, 1B, etc)
and he has a TableC (related to TableA) which is the reservations for each table (name, date, time).
He wants to represents restaurant tables using button bars with icons (conditionally calculated so grey are empty tables and red are busy tables).
Now he just gave me a copy of his file.
I will try to change his solution having in TableA 300 records instead of 300 fields.
I will need, if you have time, suggestions how to design the dwindling list since I never did one before.
Thank you for your attention !
That's not a great way to do things. Essentially instead of having a record for each "table" (seating area) with fields for location, number of seats, etc.. He has created a field for each of them.
This is de-normalized data that is just difficult to work with.
If he changes TableA to just have a record for each table, instead of a field of each table, then you should be able to build a system easier from that. You would be able to get rid of TableB completely.
Essentially, you have "Tables" and "Reservations", that's it. From there you should be able to build a calendar showing when each Table record is reserved based on a relationship from the Reservations table. And you should be able to build a system that only shows related "open" tables when creating a new reservation.
I did it.
only two Tables: "Tables" "Reservations", linked with "table" field (allowing creation of records on "Reservations")
On first run itself generates 300 records on "Tables".
He likes to have a layout where he can visualise all 300 "Tables" together using Buttons.
I guess I will do that on "Tables" using for each records the GetNthRecord function ?
An answer to your original question, n fields that must have different values. (For the rest, Mike is ofc right and you should follow the path he indicated).
1) Implement this custom function:
2) create a calc field myFieldList defined as List(yourfield_1; yourfield_2; .... yourField_n)
3) if valueCount(myFieldList) = valueCount(UniqueValues(myFieldList)), there are no fields that contain the same value as other fields, otherwise yes.
you mean step 3 as a trigger for each field right ?
no, I mean it as a trigger on the record commit. Or as a check to be done when you click submit.
Ok Thank You !
at least helping this friends I learn more
I need now to solve his problem of having all the 300 records on same layout each with icons (no portal).
About this thread who should get "Correct Answer" or "Helpful", you or Mike ?
For having all the 300 records of the same filed showing in the same layout (without using a portal) do I necessarily need to use the GetNthRecord function ?
I guess you mean 300 fields, not records. At least, standing to the original post.
I did change my friend's solution accordingly with Mike suggestion: only two Tables: "tables" and "reservations", linked with "table" field (allowing creation of records on "Reservations")