Would it be easier to just use one field and set it up as a radio button? then authenticate your "Return Date" field to not allow the field to be empty if this field is set to "Returned"?
Are all of the fields in the same table? If so a straight forward/simpel autoi-enter calculation should work. If there are in a different table then you need something in the local table to trigger the calculation. You can add a field and put it in a Let statement. Then have a script change the value of the new field when ever one of the key tems has changed. This could be done with an script trigger.
Good Morning, Bruce.
All three fields are in a related table and sit in a portal in the layout in question. When a new record is created, the item is always marked as on consignment. Currently I have On consignment set to auto enter a calculated value which says that If Sold = Yes then On consignment does not equal yes otherwise Yes. I do not have checked 'Do not replace existing value and I have checked to do not evaluate if all fields are empty. BUT now it will not let me check On consignment to get started. If I tell it DO NOT replace existing value, I can check On Consignment but when I click sold it does not change the On consignment value.
Sold and On consignment are check boxes with one value: YES. If it is not checked then it implies NO. Return Date is a Date field.
I am not familiar with LET statements and will look that up.
It seems to me this has something to do with the way FM evaluates the calculation in the auto enter.
I have a script that will fire with a button, but the employees want the least amount of room for error(forgetting) to change the status of an item.
Thanks for commenting and I look forward to learning more
If Sold is not Yes then it is empty, so On Consignment does not get evaluated. Uncheck "do not evaluate..." and you should be fine.
STill does not let me check On consignment!!!
This is my sticking point since last night.
I have put together some script triggers and that is almost as good. Struggling with the Date field.
Can't wait to see what I am still doing wrong.
How about this:
If ( not IsEmpty ( Sold ) or not IsEmpty ( Return_date ) ; "" )
Also make sure that the "Prohibit modification of value during data entry" is NOT checked
First a Thank You to all who have replied. The hardest part of this is making sure I provide all of the necessary details. Seems I was missing a part of my IF stateement.
So here is what is working:
No check in Prohibit modification
No check in Do not evaluate if all referenced fields are empty
And the calculation is as follows:
If(Sold = "Yes" or Return Date > 0; On consignment =/ "Yes"; "Yes")
I had left out the last yes and that seems to have solved my issues.
Thank You. I always learn something when I post.
I just read this thread. Glad you got it sorted out. Generally, if you don't have to use calculations, you're better off using scripts and and script triggers to get your desired result.
You could send the state of the fields and use an if statement to check for validity. Then set your marker accordingly. All with a script that runs when the field is modified or saved.
Hope this helps.
agnes b. riley . filemaker and web development
T 201-299-6223 (NJ) . 212-842-8830 (NY) . 917-660-7221 (C)
FileMaker Certified in 10 and 11
Since I am always interested in learning, I have a few questions:
1. What does it mean to set the state of the Field?
2. Why is scripting better than calculations?
3. Can the script trigger be a button? Do you have some script triggers you like better than others?
Thanks for you help.