Problem with onObjectValidate on a Related Field

[hsmith40]

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

[comment]

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.

[hsmith40]

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

[LaRetta]

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.  :-)

[hsmith40]

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.

[LaRetta]

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.  

[hsmith40]

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

 

Thanks

[hsmith40]

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.

[comment]

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.

[hsmith40]

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.

 

boxestb copy 2.fmp12.zip

[LaRetta]

...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. :-)

[hsmith40]

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?

[LaRetta]

OnObjectExit with the second script I provided would do it.

[Wim Decorte]

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

http://www.soliantconsulting.com/blog/2014/01/script-triggers-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.

[LaRetta]

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

[hsmith40]

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.

[hsmith40]

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

http://www.soliantconsulting.com/blog/2014/01/script-triggers-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.

[LaRetta]

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.  

[comment]

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!

[hsmith40]

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

[LaRetta]

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?

 

Value Lists, even if using pop-up menus and drop-down lists, work best for sets under 50.  Since this is a LineItems portal on an Invoice, I would probably include another field such as Model which can filter the list for you.  If you first select the Model, you can use conditional value list to then display only boxes for the model specified. 

 

Another option is to provide a portal in a popover or window.  You can filter the portal in similar fashion.