Jump to content

  •  

Photo

Problem with onObjectValidate on a Related Field

onobjectvalidate related field

  • Please log in to reply
20 replies to this topic

#1 hsmith40  member

hsmith40
  • Members
  • 10 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Novice
  • Time Online: 8h 37m 20s

Posted 25 January 2014 - 08:36 AM

I am doing an order entry system.

 

I have an "Order items" table that relates to a "Products" table via a "ProductNum" field.  I am using a portal to display the product info when the "ProductNum" field is keyed in.  

 

It works fine as long as the ProductNum exists in the Product table. Otherwise, I get no product info.

 

I set an onObjectValidate trigger on the "ProductNum" field to run a script that would validate the ProductNum.  The script does a count(Products::ProductNum) but it always returns 0 even if the Product Number is valid.

 

For testing, I changed the trigger to onObjectExit and it works, but that trigger causes other problems; namely, it fires when you attempt to delete a record.

 

Thanks


  • 0

#2 comment  consultant

comment
  • Members
  • 23,499 posts
  • Time Online: 299d 9h 51m 12s

Posted 25 January 2014 - 10:38 AM

The problem is not with OnObjectValidate, but with the way you're trying to use it. The script runs before the field has been validated or saved - therefore no related record exists yet (or, if the field has been modified, the related record is the one that matches the original field value).

 

Try triggering the script OnObjectSave or OnObjectExit.

 

 

I changed the trigger to onObjectExit and it works, but that trigger causes other problems; namely, it fires when you attempt to delete a record.

 

There must be something else you have there. Deleting a record does NOT trigger a script attached to a field.


  • 2

#3 hsmith40  member

hsmith40
  • Members
  • 10 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Novice
  • Time Online: 8h 37m 20s

Posted 25 January 2014 - 11:05 AM

The problem is not with OnObjectValidate, but with the way you're trying to use it. The script runs before the field has been validated or saved - therefore no related record exists yet (or, if the field has been modified, the related record is the one that matches the original field value).

 

Try triggering the script OnObjectSave or OnObjectExit.

 

 

 

There must be something else you have there. Deleting a record does NOT trigger a script attached to a field.

OnObjectSave works.  with OnObjectExit, not only does it fire the script if I delete the record or the relation, but it also fires the script if I attempt to go into layout mode.  

 

No matter, looks like OnObjectSave will do the trick.

 

Thank you for your help


  • 0

#4 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,515 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Cross Platform
  • Time Online: 166d 7h 31m 37s

Posted 25 January 2014 - 11:11 AM

with OnObjectExit, not only does it fire the script if I delete the record or the relation, but it also fires the script if I attempt to go into layout mode.  

 

 

Comment is correct and it would be good to understand what is happening here.  :-)


  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#5 hsmith40  member

hsmith40
  • Members
  • 10 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Novice
  • Time Online: 8h 37m 20s

Posted 25 January 2014 - 09:13 PM

Comment is correct and it would be good to understand what is happening here.  :-)

It does the same thing with both FM Pro 12 and FM Pro 13 Advanced.

I can supply field info, screen shots, etc if you think it would help.

 

H.


  • 0

#6 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,515 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Cross Platform
  • Time Online: 166d 7h 31m 37s

Posted 25 January 2014 - 10:21 PM

I would like to see the file or if you can create a small sample and replicate it.  The thing is ... your assumption about OnObjectExit is incorrect and if you let this go, you will be ignoring a powerful tool.  This is a great opportunity to sink in a principle once it is uncovered.  As Comment says, something else must be going on.  And sometimes unexpected behaviours and even bugs are uncovered.  Only by getting to the bottom of it can we get to the bottom of it.

 

You will need to zip your file.  You can just include a clone with only a few dummy records and tell us the layout; whatever is easiest for you.  But screen shots will do nothing to help here.  We need to see what you mean.  Thanks for bringing this up.  As you know, there are always reasons and pinning the logic on this stuff is important.   :)


  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#7 hsmith40  member

hsmith40
  • Members
  • 10 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Novice
  • Time Online: 8h 37m 20s

Posted 26 January 2014 - 09:26 AM

Thank you LaRetta.  I will create a scaled down Products table that I can zip and post. 

 

Thanks


  • 0

#8 hsmith40  member

hsmith40
  • Members
  • 10 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Novice
  • Time Online: 8h 37m 20s

Posted 26 January 2014 - 09:56 AM

Thank you LaRetta.  I will create a scaled down Products table that I can zip and post. 

 

Thanks

Sorry, I meant to say a Database that contains a scaled down Products table. :-)  The Inventory part of the database is live data.  I am adding the order entry part.


  • 0

#9 comment  consultant

comment
  • Members
  • 23,499 posts
  • Time Online: 299d 9h 51m 12s

Posted 26 January 2014 - 10:54 AM

it would be best if you could reproduce the problem in a new file - containing nothing that is not necessary for the problem to occur and be observed.


  • 0

#10 hsmith40  member

hsmith40
  • Members
  • 10 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Novice
  • Time Online: 8h 37m 20s

Posted 26 January 2014 - 05:19 PM

I have pulled everything except that which is necessary to run the test.

 

Notes:

1. Go to the "Orders" layout.  Tab to the "Serial Number" field.  Valid Serial Numbers are 1, 2, 3, 4, 5 and 6.  However, it doesn't matter if the field contains a serial number.

 

2. As long as the "Serial Number" field is active, if you 

a. Delete a record

b. Enter Layout mode, or

c. Quit FM

the script will be invoked.  Currently the script simply exits.  The original purpose of the script is to test for a valid serial number

 

Thanks again.

 

Attached Files


  • 0

#11 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,515 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Cross Platform
  • Time Online: 166d 7h 31m 37s

Posted 27 January 2014 - 10:59 PM

...but that trigger causes other problems; namely, it fires when you attempt to delete a record.

 

... not only does it fire the script if I delete the record or the relation, but it also fires the script if I attempt to go into layout mode.  

 

Hello hsmith!

 

Thank you for providing a sample (script is attached to Box Serial Number field in OrderItems portal on Orders table):

Go to Field [ Order Items::Box Serial Number ]
Exit Script [ ]
Set Variable [ $count; Value:Count(boxestb Converted::ser_numb) ] 
If [ $count > 0 ]
Exit Script [ Result: True ] 
End If
Exit Script [ Result: False ]

I believe it is a difference of semantics; of course if your cursor is in the field, trigger will fire but OnObjectExit does not fire when you delete a record, just as Comment stated, nor when you go into layout mode.  It is the field exit which is firing the script and it is doing exactly what it should do as any script trigger will do.

 

Your script can be shortened a bit.  Just checking for ANY (actually the FIRST) related record is faster than counting the related records to see if there is at least one:

If [ not boxes Converted::ser_numb ]  //  invalid serial number because NO related record found
Show Custom Dialog [ "ERROR" ; "You must enter a valid serial number" ]
Exit Script [ False ]
End If 

I notice your serial number is text in OrderItems and Boxestb.   This portion not boxes Converted::ser_numb  is a boolean test so if there is ever a possibility that your 'serial_number" will contain text only then change it to:  IsEmpty ( boxes Converted::ser_numb )

 

BTW, you will not need the OrderItemsID in the Orders table. :-)


  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#12 hsmith40  member

hsmith40
  • Members
  • 10 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Novice
  • Time Online: 8h 37m 20s

Posted 28 January 2014 - 07:08 AM

Thank you for taking the time to check this out.

 

Perhaps it would be better if I explained what I am trying to do.  I simply want to verify that the serial number (which in reality is A/N) is valid.  Disregarding my current attempt, what is the best way to accomplish this?


  • 0

#13 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,515 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Cross Platform
  • Time Online: 166d 7h 31m 37s

Posted 28 January 2014 - 09:29 AM

OnObjectExit with the second script I provided would do it.


  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#14 Wim Decorte  Carpal Tunnel

Wim Decorte
  • Moderators
  • 3,284 posts
  • LocationPhiladelphia
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 11, 12
  • Membership:TechNet
  • Time Online: 23d 11h 55m 17s

Posted 28 January 2014 - 09:38 AM

I've posted the test file before elsewhere in the forum but here's the official link to it:

http://www.soliantco...rs-filemaker-13

 

The TriggerTest file is very handy to figure out the exact sequence in which events fire. That's typically what catches us out.


  • 0

#15 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,515 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Cross Platform
  • Time Online: 166d 7h 31m 37s

Posted 28 January 2014 - 10:03 AM

I use your file quite often, Wim.  Thanks for bringing it up here.  


  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#16 hsmith40  member

hsmith40
  • Members
  • 10 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Novice
  • Time Online: 8h 37m 20s

Posted 28 January 2014 - 11:39 AM

OnObjectExit with the second script I provided would do it.

Again, thank you.  

 

Here's the scenario:

1. New record

2. Fill out form, including VALID serial number, tab to next field.

3. Cursor is now sitting in next serial number field which, of course, is blank because I am finished with that record.  And, blank is an invalid serial number

4. Attempt to enter new record.

5. Script is activated, error msg posted, cursor goes back to blank field.

6. I am stuck.

 

Since I am new to FM, there is obviously something I am missing.


  • 0

#17 hsmith40  member

hsmith40
  • Members
  • 10 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Novice
  • Time Online: 8h 37m 20s

Posted 28 January 2014 - 01:03 PM

I've posted the test file before elsewhere in the forum but here's the official link to it:

http://www.soliantco...rs-filemaker-13

 

The TriggerTest file is very handy to figure out the exact sequence in which events fire. That's typically what catches us out.

Thank you Wim, it appears to be an excellent document. I will take time to read it over carefully.


  • 0

#18 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,515 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Cross Platform
  • Time Online: 166d 7h 31m 37s

Posted 29 January 2014 - 09:42 AM

Wait a sec.  All you are doing is wanting to make sure a serial number in your LineItem (Order Items) is a valid serial number of a product, right? And you already have an All Boxes value list.  Then, even with your cursor in the field or before committing, you can validate whether the serial you enter is a member of All Boxes.  

 

You should regardless attach validation to your OrderItems box serial number.  Go to your field definitions in Order Items and set it (at the validation tab) as member of a value list and select All Boxes.  Triggers should never replace critical field-level validations only enhance the User experience and assist.  You will probably want to uncheck 'allow user override' and provide a message.  It would depend upon your business rules.

 

Now attach script to your Box serial in your Order Items portal.  Use OnObjectValidate.  This will fire before field-level validation so you can nicely tell your User to correct their entry before the strict field-level validation fires.  Change the test in your If[] ... on the second script I provided to test whether member of value list.  Be sure to match the value list name exactly in that calculation because FM will not warn you if it is incorrect.  Then change the test to this:

If [ IsEmpty ( FilterValues ( Order Items::Box Serial Number  ; ValueListItems ( Get ( FileName ) ; "All Boxes" ) ) ) ]
 // invalid serial number because not in the Product value list
Show Custom Dialog [ "ERROR" ; "You must enter a valid serial number" ]
Exit Script [ False ]
End If

See how you go with that.  


  • 1
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#19 comment  consultant

comment
  • Members
  • 23,499 posts
  • Time Online: 299d 9h 51m 12s

Posted 29 January 2014 - 01:05 PM

Wait a sec.  All you are doing is wanting to make sure a serial number in your LineItem (Order Items) is a valid serial number of a product, right? And you already have an All Boxes value list.  Then, even with your cursor in the field or before committing, you can validate whether the serial you enter is a member of All Boxes. 

 

Great example of out-of-all-boxes thinking!


  • 0

#20 hsmith40  member

hsmith40
  • Members
  • 10 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Novice
  • Time Online: 8h 37m 20s

Posted 29 January 2014 - 01:18 PM

That works Great!  

I am glad you were observant enough to catch that "All Boxes" list that I had created.  My intentions were to use it in some way but I was not aware of the functions you suggested.  Alas, I need to stick to my book and it's tutorials and quit trying to program the project :-)

 

I have one final question: in reality, the serial number is unique to every box that we have built, therefore, the boxes table currently contains about 900 entries.  Is it unreasonable use a value list that large?

 

A little background: before becoming a Jewelry and Keepsake box builder, I worked many years as a Systems Analyst dealing with Oracle, MYSql, C++ and Objective C.  When we started the box business, I wrote a dandy inventory system in Objective C using MYSql.  However, the business has grown now and we need a more encompassing system with tables for Order Entry, Customer, etc.  Hence the move to FM.  FM converted all the inventory tables beautifully and gave us some very nice added features of find, etc.  I am very happy with FM

 

Thanks for your dilligence


Great example of out-of-all-boxes thinking!

Hah!  We get that all the time :-)


  • 0




FMForum Advertisers