Update a customer (UPDATE)

best practice for production apps is to use rest as the data layer to access data and not directly integrate to sql using the sql data provider the sql data provider will be squiggled in blue to indicate it is not recommended, together with a message to use rest docid\ jrbansm ojn3nf4 dn hu instead see rest endpoints from azure sql docid\ eoui2cpyynsdruk tobdp for more information scenario this example uses a default jig with a form that executes an sql command to update a customer record resources scripts for creating azure sql tables and stored procedures database scripts docid\ jq3eq0tn7t5yl8vng 7qh configuring the sql connection docid\ o8lfnt4lwq1fvfvg1lelh this sample depends on list a single customer (select) docid 3dnsgxfk2dfatknpumaj1 jigx code the azure sql docs solution is on github how it works the execute entity action allows you to specify the function parameters and their values, as well as the data properties for the sqlite table you have more granular control over the values being saved and can include expressions the example below uses an execute entity action and maps both the parameters of the {{jig}} function and the sqlite data in the action's configuration to improve the user experience, data displayed after it has been created or updated should be updated in the local sqlite database and the backend sql system at the same action if the data is only submitted to the backend system, it must be synced back to the device before the local tables are updated, and the information can be displayed this can cause a significant lag and latency in the user's experience the example below updates the data in azure sql and the sqlite database on the device when the user presses the save button this is the best practice for building responsive user experiences when working with remote data see the data lifecycles in jigx docid\ p4z nueuokrdqozrdxa8g section of the documentation for a detailed explanation functions a store procedure based version of update customer jigx the stored procedure was designed to create a new record in azure sql if no matching id is found if the id already exists, the azure sql record is updated the same stored procedure is used for creating a new customer and updating a customer update customer jigx # jigx sql function executing a stored procedure to create a new customer record provider data provider sql connection customer azure # use manage jigx com to configure a sql connection method execute #use sql stored procedure to interact with the data in sql procedure sp insertorupdatecustomer \# the stored procedure parameters are automatically populated by jigx with the matching function parameters parameters customerid type string location input required true firstname type string location input required true lastname type string location input required true email type string location input required true phonenumber type string location input required true addressline1 type string location input required true addressline2 type string location input required false city type string location input required true zipcode type string location input required true state type string location input required true country type string location input required true a query based version of update customer jigx the sql query version of create customer jigx below only creates a new record it does not contain update like like the stored procedure above the only reason for this difference is to provide an alternative example and sql logic create customer jigx # jigx sql function executing a sql query to update a customer record in azure sql provider data provider sql connection customer azure # use manage jigx com to configure a sql connection method query #use sql statements to interact with the data in sql query | update customers set first name = @firstname, last name = @lastname, email = @email, phone number = @phonenumber, address line1 = @addressline1, address line2 = @addressline2, city = @city, state = @state, zip code = @zipcode, country = @country where id = @customerid \# jigx automatically replaces the tokens in the sql query with the matching function parameters parameters customerid type string location input required true firstname type string location input required true lastname type string location input required true email type string location input required true phonenumber type string location input required true addressline1 type string location input required true addressline2 type string location input required false city type string location input required true zipcode type string location input required true state type string location input required true country type string location input required true jigs modify the view customer jig the viewcustomers jigx file must be modified to include a jig level action, adding the edit a customer button when pressing the action button at the bottom of the viewcustomers {{jig}} , {{jigx}} will navigate to the editcustomer {{jig}} the customer's id is used as a parameter in the goto action the custid parameter is passed to the viewcustomer {{jig}} listcustomers jigx # a sample jig that uses a sql function to return and display a customer's details from azure sql \# the title property uses a jsonata expression to concatenate the customer's first and last names as the title of the jig title =@ctx datasources mydata first name & ' ' & @ctx datasources mydata last name description view customer details from azure sql type jig default \# header section displaying an image at the top of the screen header type component jig header options height medium children type component image options source uri https //images unsplash com/photo 1553413077 190dd305871c?ixlib=rb 4 0 3\&ixid=mnwxmja3fdb8mhxwag90by1wywdlfhx8fgvufdb8fhx8\&auto=format\&fit=crop\&w=1035\&q=80 \# onfocus is triggered whenever the jig is displayed the sync entities action calls the jigx sql function and populates the local sqlite tables on the device with the data returned from azure sql onfocus type action sync entities options provider data provider sql entities \ entity customers function get customer functionparameters customerid =@ctx jig inputs customerid datasources \# the mydata data source selects the data from the local sqlite database \# the where clause in the query contains a token for a parameter called customerid that is defined in queryparameters and is passed to the viewcustomer jig as an input from the listcustomer jig \# the isdocument property for the mydata datasource is set to true as a result, the data source will return as a single record to be displayed on a form instead of an array of records mydata type datasource sqlite options provider data provider local entities \ entity customers query | select id, '$ first name', '$ last name', '$ email', '$ phone number', '$ address line1', '$ address line2', '$ city', '$ state', '$ zip code', '$ country' from \[customers] where id = @customerid queryparameters customerid =@ctx jig inputs customerid isdocument true children \# the data on the jig is displayed using an entity control and entity fields if the data source returns an array, an entity control will automatically show the first record \ type component entity options children \ type component entity field options label customer id \# the value of each field points to the field in the mydata data source value =@ctx datasources mydata id \ type component field row options children \ type component entity field options label first name value =@ctx datasources mydata first name \ type component entity field options label last name value =@ctx datasources mydata last name \ type component entity field options label email value =@ctx datasources mydata email contenttype email \ type component entity field options label phone number value =@ctx datasources mydata phone number contenttype phone \ type component entity field options label address line 1 value =@ctx datasources mydata address line1 \ type component entity field options label address line 2 value =@ctx datasources mydata address line2 \ type component field row options children \ type component entity field options label city value =@ctx datasources mydata city \ type component entity field options label state value =@ctx datasources mydata state \ type component entity field options label zip value =@ctx datasources mydata zip code \# edit customer button to navigate to the newcustomer jig actions \ children \ type action go to options title edit customer linkto updatecustomer parameters custid =@ctx datasources mydata id the update customer jig use an execute entity action to submit the values of the components to the function to update azure sql and to save the new customer to the local sqlite database when the execute entity action type is used to save the values of the controls on a form, the form is unaware of the saved state, and isdiscardchangesalertenabled needs to be set to false to avoid seeing the dialog even when data has been saved set the components on the form's value properties to the values from the mydata data source to display the existing values of the customer newcustomer jigx title ='update ' & @ctx datasources mydata first name & ' ' & @ctx datasources mydata last name description update a customer's information and save it to sql azure type jig default \# header section displaying an image at the top of the screen header type component jig header options height medium children type component image options source uri https //images unsplash com/photo 1553413077 190dd305871c?ixlib=rb 4 0 3\&ixid=mnwxmja3fdb8mhxwag90by1wywdlfhx8fgvufdb8fhx8\&auto=format\&fit=crop\&w=1035\&q=80 \# ensure we have the latest information for the customer by syncing the customer's data from azure sql \# onfocus is triggered whenever the jig is displayed the sync entities action calls the jigx sql function and populates the local sqlite tables on the device with the data returned from azure sql onfocus type action sync entities options provider data provider sql entities \ entity customers function get customer functionparameters customerid =@ctx jig inputs custid datasources \# the mydata data source selects the data from the local sqlite database \# the where clause in the query contains a token for a parameter called customerid that is defined in queryparameters and is passed to the viewcustomer jig as an input from the listcustomer jig \# the isdocument property for the mydata datasource is set to true as a result, the data source will return as a single record to be displayed on a form instead of an array of records mydata type datasource sqlite options provider data provider local entities \ entity customers query | select id, '$ first name', '$ last name', '$ email', '$ phone number', '$ address line1', '$ address line2', '$ city', '$ state', '$ zip code', '$ country' from \[customers] where id = @customerid queryparameters customerid =@ctx jig inputs custid isdocument true \# a form control with input controls are used to capture the changes to the customer's information \#the values from mydata is set to the value property of each control when the form loads children \ type component form instanceid frmnewcustomer options \# when a form submit action is used to save the values of the controls on a form, the form will warn the user when navigating away without saving the form's content \# when any other action type is used to save the values of the controls on a form, the form is unaware of the saved state, and isdiscardchangesalertenabled needs to be set to false to avoid seeing the dialog even when data has been saved isdiscardchangesalertenabled false children \ type component field row options children \ type component text field instanceid firstname options label first name \#set the value property of the control to the value returned from the mydata datasource value =@ctx datasources mydata first name \ type component text field instanceid lastname options label last name value =@ctx datasources mydata last name \ type component email field instanceid email options label email value =@ctx datasources mydata email \# the type of keyboard that will be displayed on ios or android keyboardtype email address \ type component text field instanceid phonenumber options label phone number value =@ctx datasources mydata phone number keyboardtype phone pad \# set the type of text for this field this will enforce a regex for this field of the type it is set to textcontenttype telephonenumber \ type component text field instanceid addressline1 options label address line 1 value =@ctx datasources mydata address line1 textcontenttype streetaddressline1 \ type component text field instanceid addressline2 options label address line 2 textcontenttype streetaddressline2 value =@ctx datasources mydata address line2 isrequired false \ type component text field instanceid city options label city value =@ctx datasources mydata city textcontenttype addresscity \# a dropdown control is used to list the usa states \ type component dropdown instanceid state options label state \# the data source for the dropdown options is a static datasource defined in usa states jigx data =@ctx datasources usa states value =@ctx datasources mydata state item type component dropdown item options title =@ctx current item name value =@ctx current item code \ type component text field instanceid zipcode options label zip code value =@ctx datasources mydata zip code textcontenttype postalcode \ type component text field instanceid country options label country textcontenttype countryname value =@ctx datasources mydata country style \# the dropdown only contains usa states \# set the control to read only so the value cannot be changed to an unsupported country isdisabled true actions \ children \# use an execute entity action to submit the values of the controls to the jigx function to update azure sql and to save the new customer to the local sqlite database \ type action execute entity options \# the title of the save button title update customer \# the data provider to use for the remote data this solution uses azure sql provider data provider sql \# the name of the local sqlite database that the new record will be created in entity customers \# the name of the jigx function used to save the data to azure sql function create customer \# set the function parameters to values of the controls on the form functionparameters customerid =@ctx jig inputs custid firstname =@ctx components firstname state value lastname =@ctx components lastname state value email =@ctx components email state value phonenumber =@ctx components phonenumber state value addressline1 =@ctx components addressline1 state value addressline2 =@ctx components addressline2 state value city =@ctx components city state value zipcode =@ctx components zipcode state value state =@ctx components state state selected code country =@ctx components country state value \# the command type to be executed on the local sqlite database method update \# navigate to the previous screen after the action has been performed onsuccess type action go back \# set the column values of the new record that will be created in the local sqlite customers table data id =@ctx jig inputs custid firstname =@ctx components firstname state value lastname =@ctx components lastname state value email =@ctx components email state value phonenumber =@ctx components phonenumber state value addressline1 =@ctx components addressline1 state value addressline2 =@ctx components addressline2 state value city =@ctx components city state value zipcode =@ctx components zipcode state value state =@ctx components state state selected code country =@ctx components country state value \# display a dialog box with a message if the new record is created successfully onsuccess description customer updated successfully title updated created