Check zero value before being allowed to close form

[seaken64]

I want to prevent the form from being closed if the value in a calculated field is not zero

I have a form that is used to enter historical data into a table from paper copies of our Sales Slips
I have set up two calculated fields on that form that shows the variance between what the form has calculated and what the user has entered. I want the variance to be zero in both fields before I allow the form to be closed and the record saved.

I have looked at form properties and form control properties but cannot find a way to verify the calculated field is zero before closing the form. I am now thinking I need to use a script. But I do not know how to do that. Do I need to use a script to do what I want or is there a way to do this from the Action Script menues?

If I do need to create a script where do I start? I want the script to do this:
Check the values in the two calculated fields on the form
If the value in both fields is zero than allow the user to proceed as normal (close form)
If the value is not zero in either field than display an error message and put the user back into the form to make corrections until the values are zero.

Do I put this script on the "OnExit" event? or the "CanExit" event? or somewhere else?

I've never done scripting in A5 before.

Thank you,
Sean

[amorris]

Re: Check zero value before being allowed to close form

I have not tested this, but where I would start.

on can exit, I would get the field value into a variable and test that it is or is not zero, then put the focus back to a field where it can be corrected or allow the exit(which is "do nothing")

you can do most of this with a genie.
varsettofield.pngvarsettofield2.png

[martinwcole]

Re: Check zero value before being allowed to close form

put this in the form's canexit event

if somevalue <> 0
cancel()
end if

[Tom Cone Jr]

Re: Check zero value before being allowed to close form

Sean,

why two calc fields to show a single variance value?

Please clarify some things:

1) when you say you have two calc fields, are these table fields for which you've defined calc expressions in field rules, or are these "calculated display" fields defined in the form layout but not present in your table?

2) do you mean to say you have two calc fields and you want a third (computed) value to be zero, or do you want (as you have written) each of the two calc fields in your form to be zero.

3) examine the objects displaying the two calc values in your form layout. What is each object's official "name"?

4) in your own words describe how you would use the two calc values in your form layout to arrive a computed value of zero?

Any chance you can post a sample database, containing the tables supporting your form and their accompanying dictionaries?

-- tom

[seaken64]

Re: Check zero value before being allowed to close form

Tom,

I have two calc fields that calculate two different values. One calculates the difference in the Materials values the other the difference in the Total values. I set these up on the entry form as an aid to catch typos. I look at them when I review the records but now I want to automate the entry a little more to help control mistakes by the entry person.

Answer 1) These are form fields only. I did not set these up in the table.

2) I want each calculated variance field to be zero. If something other than zero is in the variance fields then there is a typo somewhere.

3) "cmtrlvariance" is the name for the field that shows the variance between the "Materials" field (entered by the user)and the "Calculated Materials" field (calculated from the values entered into the separate category fields by the user)

"cvariance" is the name of the field that shows the variance between the "Total" field (entered by user) and the "Calculated Total" field (calculated from the Materials,Tax,and Labor fields entered separately by the user)

4) for cmtrlvariance - take the values entered into the separate category fields (Unit,Options,Chimney, etc.) and sum them and place in the field named "cmaterials". Subtract cmaterials from the value entered into the field named "materials". If the value does not equal zero there is an entry error in one of the amount fields.

for cvariance - take the values entered into the separate entry fields (Materials, Labor,Tax) and sum them and place in the field named "ctotal". Subtract ctotal from the value entered into the field named "total". If the value does not equal zero there is an entry error in one of the amount fields.

I don't think I can figure out how to send a copy. I need to figure that out later. I'll need to remove all the private data and I'm not yet sure how I'll do that.

Thanks for your help

Sean

[seaken64]

Re: Check zero value before being allowed to close form

Thanks Al,

I looked at that but I could not figure out how to "test that it is or is not zero"

[seaken64]

Re: Check zero value before being allowed to close form

put this in the form's canexit event

if somevalue <> 0
cancel()
end if

This is done with a script? How do I get a script to fire on a form event? Do I enter this script in the genie? or do I store the script somewhere and then refer to it?

Sean

[Tom Cone Jr]

Re: Check zero value before being allowed to close form

Sean,

Assuming the two calc display fields are numeric data, something close to this should do the trick

In the CanExit event for the form:

if calc->cmtrlvariance <> 0 .or. calc->cvariance <> 0 then
    msgbox("Oops","Your transaction is not in balance, check your variance values and correct before closing the form.")
    cancel()
end if

We can't see the entire data entry sequence. The solution above should solve the immediate problem but it would not prevent the user from entering incorrect data for two separate transactions back to back. why? because they wouldn't try to close the form after each transaction so the CanExit wouldn't fire until they try to close the form after the 2nd transaction. Is this clear?

You probably need to think more carefully about when you want this type of validation to occur, limiting it to attempts to close the form may not cover all the situations you may need.

[Tom Cone Jr]

Re: Check zero value before being allowed to close form

[ deleted duplicate ]

[martinwcole]

Re: Check zero value before being allowed to close form

Sean, put Tom's code in the canexit event for the form. Action scripting is not needed. In design mode, at the top of the screen, where you see Form, right click and you will see many form events - one of them is CanExit

Tom, do you think this might be better handled in the cansaverecord event in field rules?

[Tom Cone Jr]

Re: Check zero value before being allowed to close form

Martin, I'm not sure. That works well for a data entry form based on a single table, but its beginning to sound like he's got a set based form, that probably includes multiple child table records. what's needed is an event that fires before the user can leave the current transaction. -- tom

[seaken64]

Re: Check zero value before being allowed to close form

Thanks guys. Yes, this form is based on a set. And I probably should consider putting this kind of thing in the table field rules. I'll start messing around with that idea. In the meantime this solution looks like I might at least be able to show a warning message if they try to close the form before making sure the numbers are correct. I'm still a little unsure of how to get this script into the form but at least I now have a clue. I'll experiment and see if I can figure it out. It's about time I learned how to do some simple Xbasic scripts instead of just using the Active Script genies.

Thank again.
Sean

[Tom Cone Jr]

Re: Check zero value before being allowed to close form

Sean,

You may need to re-think the concept of a "record". In your first post it sounds like you use that term to refer to the entire transaction. In databases like Alpha Five the term has a very different meaning. In your set based form each "transaction" probably has a single "record" entered to the parent table in your set, and multiple "records" entered into the linked child table. The parent table record will be saved as soon as editing a new child record begins. If you are entering child table records through an embedded browse each row gets saved when the user navigates to a different row, or clicks on a field in the form outside the browse object. All of this can be managed but its not particularly easy, and is impossible for us to specify for you, given the limited amount of information you've furnished and the absence of a working sample.

[seaken64]

Re: Check zero value before being allowed to close form

Maybe a picture of the form will help?Transaction Form.png

[seaken64]

Re: Check zero value before being allowed to close form

In this case the set consists of two tables, the Transaction table and the Customer table. The set is used only to link the customer name and number to the transaction entry. I'm sure there are times when I am using multiple records from a child table but in this case each time I enter a transaction with this form I'm entering a new record into the Transaction table. The customer record is not added as it must already exist before the Transaction can be entered. In our terminology the Transaction record represents a single sales slip.