karen
Posts: 99
|
| Posted: 10/06/2011, 5:28 AM |
|
Hi all,
I'm sure this is very easy but I can't seem to get my head around it now. I have a boolean field in the database that I would like to update to 1 when the record is updated. I would like to be able to do this without using a hidden field on the record form.
So I believe I should be modifying the SQL in the before execute update event. But looking at the documentation, there are only examples to modify the complete SQL statement or the where clause. I want to be able to append that extra field in the update sql. How do I approach this?
Greatly appreciate any help to help me clear this mental block! Thanks!!!
|
 |
 |
Gena
Posts: 591
|
| Posted: 10/06/2011, 5:30 AM |
|
try to use Custom update
_________________
Gena |
|
|
|
karen
Posts: 99
|
| Posted: 10/06/2011, 5:37 AM |
|
Thanks for your instant reply, Gena! I was hoping I didn't have to go down that road but I'll use that if there are no easier ways.
|
|
|
|
datadoit
|
| Posted: 10/06/2011, 7:33 AM |
|
Why the resistance to using a hidden field? With a hidden field you may
be able to accomplish what you want with no coding.
|
|
|
|
karen
Posts: 99
|
| Posted: 10/06/2011, 2:20 PM |
|
Thanks for your input, datadoit. I've been advised that for sensitive data, I shouldn't use hidden fields. That's why I'm taking the 'long way'. What are your thoughts on this?
|
|
|
|
datadoit
|
| Posted: 10/06/2011, 7:06 PM |
|
What's so sensitive about a "1" ? :)
|
|
|
|
karen
Posts: 99
|
| Posted: 10/06/2011, 7:48 PM |
|
Haha, "1" isn't very sensitive but it's the flag for the record and I don't want the possibility of this flag being manipulated. Do you think that's possible? It is going to be a public record form so no login is required.
|
|
|
|
datadoit
|
| Posted: 10/07/2011, 7:57 AM |
|
It wouldn't matter because the hidden field's value would be set -after-
the form is submitted.
For instance, when the form is loaded, capture the field values and drop
them into variables to hold. Then in the form's OnValidate, compare
those values. If there are changes or differences, set the value for
your hidden field to 1 then, which is bound to your database field.
Again, all of this can be accomplished using Actions (no coding). For
each field that you want to compare, in it's BeforeShow add an Action to
Retrieve Value for Variable, and define your variable to
$Component->GetValue(). Then in the form's OnValidate, first add an
Action to Declare Variable, and declare it to the name of the variable
you used above, and set it's value to itself (ThisVariable =
$ThisVariable). That basically is the equivalent of saying:
global $ThisVariable;
in coding. Next add an Action to Retrieve Value for Control, choose
your hidden field control, choose Expression, then set the value to:
($ThisVariable <> $Container->TheField->GetValue()) ? 1 : null
You could also use:
($ThisVariable <> CCGetFromPost("TheField", "")) ? 1 : null
We're using PHP's ternary operator to conditionally set the value of the
control. You can add an Action to do this comparison for all of the
fields you want to check for changes on.
Now as a separate recommendation, rather than using just a boolean "1",
I would use a datetime timestamp. Gives a little more useful info.
($ThisVariable <> CCGetFromPost("TheField"), "")) ? mktime() : null
All of this is to avoid a simple two line statement in your form's
AfterUpdate event. However, the method above actually check's for
changes, versus setting your database value to 1 every time a visitor
simply clicks the Submit button, whether they actually made any changes
or not.
|
|
|
|
karen
Posts: 99
|
| Posted: 10/07/2011, 10:48 PM |
|
I really appreciate your very detailed response, datadoit! I especially like the idea of using a timestamp so I'm implementing that and using the hidden field again and assigning the value again in onvalidate. But I'm coding rather than using actions as it's easier for me to see the whole picture all in one lot when I need to make changes.
Thanks a lot everyone!
|
|
|
|
|
