1 2 3 Previous Next 36 Replies Latest reply on Dec 18, 2013 12:12 PM by Stephen Huston

    Capturing a 504 Error for a Unique Value Validation

    user12884

      I have not been able to capture the 504 Error code. I need to handle unique records with a Duplicate field in a special way but allow them to exist.

       

      Situation:

      1. I have a field from another table that is read into a text field (Paragraph) via lookup on record creation.

      2. Paragraph field contains a serial number and part number among other things.

      3. The serial number and part number are parsed into their respective fields from the Paragraph field as calculations.

      4. A calculation field (Unique SNPN) combines the two numbers and validates that the combined number is Unique in the database.

      5. In a script for transfering the Paragraph info and creating the New Record:

       

      Set Error Capture [On]

      New Record/Request

      IF [Get(LastError) <> 0]

      Show Custom Dialog ["Show Message"; "An error has occurred --> #" & Get (LastError)]

      End If

       

      Background:

      1. If I turn on the message in the Unique SNPN calculation field settings it works as planned with the dialog box. However no error code is recorded.

      2. Unique SNPN field validation set for:

      Unique Value, Only during data entry, Allow User to override during data entry

       

       

      Question:

      1. If I create the record first, then manually transfer the Paragraph into the new record and then do a commit will I be able to pick up the 504 error on a duplicate field? Or is there a more eligent method to do this?

       

      Thanks

       

      Richard K.

      303-499-1787

        • 1. Re: Capturing a 504 Error for a Unique Value Validation
          Stephen Huston

          Hi Richard,

           

          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.

           

          Stephen Huston

          1 of 1 people found this helpful
          • 2. Re: Capturing a 504 Error for a Unique Value Validation
            user12884

            Stephen,

             

            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.

             

            Thanks,

             

            Richard

            • 3. Re: Capturing a 504 Error for a Unique Value Validation
              Malcolm

              It would be nice if isValid() paid attention to the validation rules that were specified for the field. Unfortunately it does not do that.

               

              Malcolm

              1 of 1 people found this helpful
              • 4. Re: Capturing a 504 Error for a Unique Value Validation
                user12884

                Thanks,

                 

                I suspected it didn't...

                 

                Richard

                • 5. Re: Capturing a 504 Error for a Unique Value Validation
                  Mike_Mitchell

                  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.

                   

                  Mike

                  • 6. Re: Capturing a 504 Error for a Unique Value Validation
                    user12884

                    MIke,

                     

                    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,

                     

                    Richard

                    • 7. Re: Capturing a 504 Error for a Unique Value Validation
                      comment

                      user12884 wrote:

                       

                      2 years of work now held up by what I think is a serious bug with Filemaker.

                       

                      What exactly is this "serious bug" you perceive?

                      • 8. Re: Capturing a 504 Error for a Unique Value Validation
                        DavidJondreau

                        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:

                         

                        Let([

                        items = ValueListItems ( Get ( FileName ) ; "UniqueSNPN" ) ;

                        thisVal = table::Unique SNPN ;

                        result = not isEmpty ( FilterValues ( items ; thisVal ) ) ];

                        result

                        )

                         

                        Instead of checking for an error code, check for a 1 in that calc.

                        • 9. Re: Capturing a 504 Error for a Unique Value Validation
                          comment

                          David,

                           

                          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?

                          • 10. Re: Capturing a 504 Error for a Unique Value Validation
                            DavidJondreau

                            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.

                            • 11. Re: Capturing a 504 Error for a Unique Value Validation
                              comment

                              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.

                              • 12. Re: Capturing a 504 Error for a Unique Value Validation
                                DavidJondreau

                                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.

                                • 13. Re: Capturing a 504 Error for a Unique Value Validation
                                  Malcolm

                                  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

                                   

                                  p.s.

                                  You aren't going to lose two years work over such a simple problem - get some rest.

                                  • 14. Re: Capturing a 504 Error for a Unique Value Validation
                                    user12884

                                    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 -->

                                    504Value 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,

                                     

                                    Richard

                                    1 2 3 Previous Next