List a single customer (SELECT)

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 view the customer's details by pressing on the customer in the list, which opens the customer's details in a default {{jig}} 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 customers (select) docid\ nvjbmsjrmqna u9ubja7x jigx code the azure sql docs solution is on github list & view customer detail how it works this example selects a customer from the list and uses the customerid to return the customer's details to the default {{jig}} on the device, using the sql data provider's function, where it is stored in the sqlite database in the default {{jig}} the data is selected from the sqlite database using a sql query in a data source which in turn is used by the {{jig}} to render the details in entity fields the functions used to return a single record use forrowswithmatchingids true only records in the sqlite table with a matching id will be updated when forrowswithmatchingids is false or omitted, all records in the sqlite table will be deleted, and only the records returned by the stored procedure, or query statement will be inserted functions the {{jigx}} function is listed twice, once for executing a stored procedure and once for executing a query a store procedure based version of get customer jigx get customer jigx # jigx sql function executing a stored procedure to select only a single customer provider data provider sql connection customer azure # use manage jigx com to configure a sql connection method execute #use sql stored procedsure to interact with the data in sql query | procedure sp getcustomerbyid parameters \# the stored procedure has a required parameter called customerid therefore the value of the jigx function's customerid parameter will be passed as a parameter to the stored procedure customerid type string location input \# one of the columns returned by the stored procedure is called id only records in the sqlite table with a matching id will be updated when forrowswithmatchingids is false or omitted, all records in the sqlite table will be deleted, and only the records returned by the store procedure will be inserted forrowswithmatchingids true a query based version of get customer jigx get customers jigx # jigx sql function executing a stored procedure to select only a single customer 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 | query | select id, first name, last name, email, phone number, address line1, address line2, city, state, zip code, country from customers where id = @customerid parameters \# the sql query has a where clause that contains customerid therefore the value of the jigx function's customerid parameter will be used as the value of the @customerid token in the where clause customerid type string location input \# one of the columns returned by the query statement is called id only records in the sqlite table with a matching id will be updated when forrowswithmatchingids is false or omitted, all records in the sqlite table will be deleted, and only the records returned by the query statement will be inserted forrowswithmatchingids true jigs modifying the list customers jig the listcustomers jigx file must be modified to include an onpress action when pressing on a list item in the customer list, {{jigx}} will navigate to the viewcustomer jigx customerid is specified as a parameter that should be passed to the viewcustomer jigx, where it is used in the where clause of the sqlite query to load the selected customer add the onpress code to the bottom of the listcustomers jigx file listcustomers jigx # a sample list jig that uses a sql function to return and display a list of customers from azure sql title list customers description show a list of all customers in a sql database type jig list icon contact \# 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 customers \# the mydata data source selects the data from the local sqlite database datasources 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] \# the list and its list items are configured below this is a list jig; therefore, its properties, such as data and item, are top level properties \# the data property binds the list to a specific data source data =@ctx datasources mydata \# the item property specifies the list item type and its attributes item type component list item options title =@ctx current item first name & ' ' & @ctx current item last name subtitle =@ctx current item email description | \=@ctx current item address line1 & ' ' & @ctx current item city & ' ' & @ctx current item state & ' ' & @ctx current item zip code label title =@ctx current item country leftelement element avatar \# the text property of the left element is specified using a jsonata expression that builds a two letter string by concatenating the first letters of the customer's first and last names text =$substring(@ctx current item first name,0,1) & $substring(@ctx current item last name,0,1) divider solid \# a go to action is triggered when pressing on a list item onpress type action go to options \# the name of the jig to navigate to when the item is pressed linkto viewcustomer parameters \# the id column of the current item being pressed on is passed as a parameter called customerid to the viewcustomer jig customerid =@ctx current item id view customer jig 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 the data on the {{jig}} displays using an entity and entity fields component if the data source returns an array, an entity component will automatically show the first record the yaml for viewing the selected customer is viewcustomer 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 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 index add the list of customers {{jig}} to the home screen index jigx name azure sql docs title azure sql docs category analytics tabs home jigid listcustomers icon home apps logo