Script-trigger to delete records automatically

[Chemadian]

Hey everyone,

I am looking to get some advice on how to define a script-trigger that will automatically delete records that have the "AmountOnHand" field = zero. A little background on the reason i need this solution is that my database manages chemical inventory. Each chemical has a particular lot #, therefore each chemical ingredient needs a separate record. This creates the problem that when we use up all of a chemical the record still remains,even though we are using an ingredient with a different lot #. I've thought about running a weekly report that finds the set with "AmountOnHand" = zero and then deletes the found set, but anyone have any suggestions or a better way? Any help is appreciated!

[Fitch]

Is your question how to attach a script trigger to a field, or how to create a script that deletes records, or what?

Does a user manually set the AmountOnHand field to 0?

[Chemadian]

Hey Fitch,

I know how to attach a trigger to a field, I'm guessing the trigger i want is "OnObjectModify", I just want to make sure i'm clear on the script steps needed to solve the problem in my first post. My lack of experience might show through because i want to use the IF function for my script steps but i'm not sure if thats the way to go!

No the "AmountOnHand" is automatically reduced (to zero) through the ingredient being used up in a chemical Formulae.

[comment]

This creates the problem that when we use up all of a chemical the record still remains

Why is this a problem? Don't you need the record for history purposes?

[Fitch]

It's not clear to me how your tables are related, what fields are in what tables, and what layout (based on what table occurrence) you are on when the quantities get changed, and whether that process is scripted or not.

Your trigger would most likely not be on object modify, but rather on object validate, exit, or save.

If[ AmountOnHand =0 ]

... do stuff here

End If