• Blog posts

    Bio

    By David Crowther

    SQL___CASE_WHEN_Update_-_1.png

    Question:

    How do you use the values in one field to auto update values in a different field?

    Answer:

    Using the DynamicMaps webGIS – MapThat – we have users editing a Planning Apps Layer choosing to insert/update a value of either Passed, Submitted or Declined into the Progress field.

    SQL___CASE_WHEN_Update_-_2.png

    Once the user presses Save the changes are committed…

    SQL___CASE_WHEN_Update_-_3.png

    …with the new record inserted into the source database table, with the value Passed in the Progress field.

    SQL___CASE_WHEN_Update_-_4.png

    However, you may wish another field to be used to store the CODE for the Progress. So here we have updated the DESIGN of the table to include an extra field called Progress2.

    SQL___CASE_WHEN_Update_-_5.png

    To automatically update this new Code field (Progess2) we can apply a TRIGGER to the Database table. The Trigger will run on Insert and Updates into the table and update the Progress2 field with a value based on the user’s choice for the Progress field.

    • Passed = P
    • Submitted = S
    • Declined = D

    Here are the Trigger details for my table and field names.

    SQL___CASE_WHEN_Update_-_6.png

    Once ran the new Trigger is created it is attached to the table.

    SQL___CASE_WHEN_Update_-_7.png

    If we now make a change to the Planning Apps table, for example using the webGIS MapThat, once the user creates a new record and chooses a value for the Progress field e.g. Submitted,..

    SQL___CASE_WHEN_Update_-_8.png

    … the new record will be insrted into the source table, and the trigger will auto update the Progress2 field with the value of ‘S’ based on the triggers CASE WHEN statement -

    SQL___CASE_WHEN_Update_-_9.png

    Triggers are a great way to auto update fields in your source tables. And in this case, it is a great way to allow the user to choose a Well-Known Value e.g.. ‘Submitted’ from a list, but then update your source table with the actual CODE required ‘S’.

Comments

0 comments

Please sign in to leave a comment.