1 of 1 people found this helpful
I notice that you have validation set for "Only during data entry" in this case.
That setting sometimes results in data which is looked up or auto-entered not being validated unless you are editing the field to be validated.
You could run a test in the script using the IsValid (your field) to trigger something if it's not valid after it has been set/entered. That's actually what the Is Valid test is for rather than the obsolete practice of testing whether or not a value exists.
Thank you - I was not aware of the IS Valid Command. Interesting.
However, it does not appear that it will let me Validate based on being a duplicate value or unique value. If that is untrue let me know...
I could write a search and retrevial comparision but it is not as fast as the Unique value validation. This is a very large database in the number of records. Speed is very important financially.
1 of 1 people found this helpful
It would be nice if isValid() paid attention to the validation rules that were specified for the field. Unfortunately it does not do that.
I suspected it didn't...
In a situation like this, I might be tempted to do it with script. What if you replace the New Record menu command with a script using Custom Menus? That would allow you to do what you suggest - transfer the field manually.
Might be worth a look.
Thanks for the suggestion. The application will have many "untrained" end users and this script is just part of a much larger automation script. The only place the entire automation script stops is when there is a duplicate serial number / part number. I need to recognize the duplicate and automatically mark the record and continue the master script.
2 years of work now held up by what I think is a serious bug with Filemaker. Otherwise we are ready to launch. This is my last type 1 error scenareo. I am thinking I may have to creat a paralell table and do the unique check manually against the other table and not trust FileMaker's validation capabilities. It would cause a speed degredation and a financial hit until Filemaker fixes the bug. I was hoping someone else had dealt with this one and found a more eligant solution.
All the best,
Let me rephrase so it's clear that I understand the problem:
You want to create a record and have it autofill data. A calculation in that record returns a result of that autofilled data. If that result is the same as another record's, you want the record to still exist, but show a warning message to the user.
If that's an accurate summary, I would ditch the idea of validation. Validating won't allow the record to persist.
Instead, create a value list based on the field Unique SNPN. Check to see if your new record's SNPN exists in that value list.
The calc could be:
items = ValueListItems ( Get ( FileName ) ; "UniqueSNPN" ) ;
thisVal = table::Unique SNPN ;
result = not isEmpty ( FilterValues ( items ; thisVal ) ) ];
Instead of checking for an error code, check for a 1 in that calc.
I agree that the original description is not clear enough; for one thing, a calculation field cannot be validated, so...
However, I am not sure I understand your suggestion either: if the value list is based on the field, under what circumstances would the calculation return false?
Michael, I don't think the ValueListItems() will update until after the record has been committed (even with a Set Field)
If that does turn out to be an issue, since this is all being scripted, I would modify my suggestion to change the "calc" field (probably an auto-enter calc) to remove the auto-enter and set the field after the "validation" check has been done.
If there is a need for the auto-enter to exist, he could use an additional field to run the check on.
I don't see at what point of time this check would be done. Moreover, if a record is edited and the value is not modified (or modified and returned back to the original) it will conflict with itself.
Well, it's clear to me from my reading of the original question, but I don't think further discussion would be fruitful without Richard K's input.
Creating a value list from a field has an interesting property, it is a unique list of all values, so this method has some application here. It's quick too.
Another method would be to create a relationship to do this test prior to creating the record.
1. Control record creation using custom menus to pass the New Record command to a script.
2. Create a global relationship based on a global field/s relating to SN and PN.
Within your script:
3. Before creating a record put the Unique SNPN into global fields.
4. Test for the existence of a valid relationship using isEmpty(SelfJoinTest::SerialNumber)
5. Proceed to create a new record or not
You aren't going to lose two years work over such a simple problem - get some rest.
Michael, David and others,
The record is based off of a large input field. The field is parsed and key data strings are pulled out. The pulled out string data is parsed again and it contains many things but a serial number and a part number are included within the information. The program takes the input information and sorts the serial number into the serial number field and the same for Part number, then it calculates a combined serial number and part nuimber field (SNPN) to see if the original input contains duplicate product information.
If the SNPN is unique, various other tests are run on the record and a report is generated. If it is not unique then the input is kept and the record is marked as a duplicate, other tests are run to see if the entire core information is a duplicate or if just a part of the information is duplicate. Depending on those answers various reports are provided back to the user and a condition is set on the record.
The SNPN field originally had Unique set as a validation (I didn't know Validation of a calculation field would not work even though you can set it as paramters - Bug number 2 if true). I don't want the message warning box to pop up at all, but want to handle it in code instead. I was originally trying to capture a 504 Error code -->
504 Value in field is not unique as required in validation entry options
"Value in field is not unique as required in validation entry options"
I have since found out that Filemaker has an apparent "known but poorly documented bug" and will not return the 504 error with a Get (LastError) command after Error Capture is turned on. Thus there is no way to set an IF condition and branch to a elegant solution. I am now resigned to the fact that I will need to create a parallel table containing a set of SNPNs and manually validate against that table upon every new record commit.
The positives are:
1. I can still get the job done and provide one button automation for end user simplicity
The negatives are:
1. More code that needs to be written and managed versus being managed at the field level by parameters of the field
2. It will be considerably slower for all input transactions
3. It increases the size of the stored database
4. Increase deletion efforts upon deltin of the original record.
5. Time is money when you are talking about throughput times on a large scale.
6. When and IF Filemaker ever fixes their Unique field validation and 504 capture, I would revert the system back. If the don't fix it, the overall speed will likely force me to a migration sooner.
Thanks to you both and others. I suspect David is right and that will be the method I pursue for the time being.
That being said - I hope FileMaker fixes the bug (or Bugs as it may be).
All the best,