User Tools

Site Tools


external_data

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
external_data [2022/11/10 16:01]
yves
external_data [2023/11/21 18:00] (current)
chris
Line 18: Line 18:
  
 ==== Configure queries ==== ==== Configure queries ====
-After creating the external database connection we need to create a link between the data and the slides in PMA.core. To accomplish this we need to define an SQL query that will instruct PMA.core how each slide is linked to one or more rows of data in the external connection. To start that we need to click ton Add button in the external data source page+After creating the external database connection we need to create a link between the data and the slides in PMA.core. To accomplish this we need to define an SQL query that will instruct PMA.core how each slide is linked to one or more rows of data in the external connection. To start that we need to click the 'Add' ​button in the external data source page
  
 === SQL query === === SQL query ===
  
 {{ ::​add_external_data.jpg?​600 |}} {{ ::​add_external_data.jpg?​600 |}}
-Even though the Add Query page seems complicated it is nothing more than an intuitive ​UI to create an SQL query so that PMA.core can link the the external data to the slides provided. The page is divided in two parts, on the right hand side there is a treeview with PMA.core slides ​that is only used for testing ​the query and on the left hand side UI to create ​the query. The parts that are needed to successfully create the query are:+Even though the Add Query page seems complicated it is just a UI to create an SQL query so that PMA.core can link the external data to the slides provided. The page is divided in two parts, ​on the left hand side is the UI to create the query, and on the right hand side there is a treeview with PMA.core slides used for testing the query. ​\\ 
 +The parts that are needed to successfully create the query are:
   * Connection: One of the external connections created earlier (see previous paragraph)   * Connection: One of the external connections created earlier (see previous paragraph)
   * Table name: This field is **automatically** populated after you select the //​Connection//​. This is the table with the data you want to link to PMA.core   * Table name: This field is **automatically** populated after you select the //​Connection//​. This is the table with the data you want to link to PMA.core
   * Display name: A name for this query, just to distinguish between this and other queries   * Display name: A name for this query, just to distinguish between this and other queries
   * Fields to include: This is a list of all fields in the table. It is **automatically** populated after selecting the //Table Name//​. ​ You can then edit this to remove any fields you do not want. To reset this you simply need to reselect the //Table Name//.   * Fields to include: This is a list of all fields in the table. It is **automatically** populated after selecting the //Table Name//​. ​ You can then edit this to remove any fields you do not want. To reset this you simply need to reselect the //Table Name//.
-  * Match mode: PMA.core offers two ways to link the external data to PMA.core slides. ​This modes are **match based on file name** or **match based on the barcode text**. This is the value that will be passed on the query to select data based upon, and in the Query Preview field is identified as **#​SLIDE_IDENTIFIER#​**+  * Match mode: PMA.core offers two ways to link external data to PMA.core slides. ​These modes are **match based on file name** or **match based on the barcode text**. This is the value that will be passed on to the query to select data, and in the Query Preview field is identified as **#​SLIDE_IDENTIFIER#​**
   * Regular expression: This is an //​optional//​ regular expression that will be applied on **SLIDE_IDENTIFIER** before passing it to the query. This way you can transform the filename or barcode to match what is expected by the database (e.x. removing the path, the filename extension etc)   * Regular expression: This is an //​optional//​ regular expression that will be applied on **SLIDE_IDENTIFIER** before passing it to the query. This way you can transform the filename or barcode to match what is expected by the database (e.x. removing the path, the filename extension etc)
-  * Filter Expression: This is the //WHERE// expression of the query, that have to match the **SLIDE_IDENTIFIER**. This can simply be a column of the table or some other valid SQL WHERE expression ​+  * Filter Expression: This is the //WHERE// expression of the query, that has to match the **SLIDE_IDENTIFIER**. This can simply be a column of the table or some other valid SQL WHERE expression ​
   * Query preview: This is an important preview of the final query. After each change to the previous fields your changes will be reflected here for final inspection.   * Query preview: This is an important preview of the final query. After each change to the previous fields your changes will be reflected here for final inspection.
  
-As stated an above the **#​SLIDE_IDENTIFIER#​** part of the query is dependent on the mode selected and the regular expression if provided. ​So this can be the file name or the barcode text of a slideafter it is transformed by the regular expression.+As stated an above the **#​SLIDE_IDENTIFIER#​** part of the query is dependent on the mode selected and the regular expressionif provided. ​This could be the file name or the barcode text of a slide after it is transformed by the regular expression.
  
-Before your query is ready you need to test that it works as expected+Before your query is ready you need to test that it works as expected:
  
 === Testing === === Testing ===
  
-To test your query for errors and validating ​its results you can use the Test query field. You can either write a slide identifier manually in the text box or use the //Set from treeview// button after selecting a slide from the treeview on the right hand side of the screen.+To test your query for errors and validate ​its results you can use the Test query field. You can either write a slide identifier manually in the text box or use the //Set from treeview// button after selecting a slide from the treeview on the right hand side of the screen.
 {{ :​test_query.jpg?​400 |}} {{ :​test_query.jpg?​400 |}}
  
-Clicking ​on the Execute button will execute the query on the server. Any errors in syntax will be reported immediately bellow the execute button. If no errors occur a popup will appear ​that will show the results of the query. ​+Clicking the "Execute" ​button will execute the query on the server. Any errors in syntax will be reported immediately bellow the execute button. If no errors occura popup will appear ​showing ​the results of the query. ​
 {{ ::​test_query_popup.jpg?​400 |}} {{ ::​test_query_popup.jpg?​400 |}}
  
 ==== Consumption and applications ==== ==== Consumption and applications ====
-After creating the queries you can now use the [[https://​docs.pathomation.com/​pma.core.api/#/​Api/​PMA.Services.Api.HistoScope.GetFormData|FormData]] API to request data by using the Query Id as the Form Id, the same way you request native PMA.core Form Data. +After creating the queries you can now use the [[https://​docs.pathomation.com/​pma.core.api/#/​Api/​PMA.Services.Api.HistoScope.GetFormData|FormData]] API to request data by using the Query ID as the Form ID, the same way you request native PMA.core Form Data. 
  
-This is the same API that is used to provide external data in downstream ​application ​like [[https://​www.pathomation.com/​pma.studio|PMA.studio]],​ [[https://​www.pathomation.com/​pma.slidebox|PMA.slidebox]],​ [[https://​www.pathomation.com/​pma.control|PMA.control]] etc. +This is the same API that is used to provide external data in downstream ​applications ​like [[https://​www.pathomation.com/​pma.studio|PMA.studio]],​ [[https://​www.pathomation.com/​pma.slidebox|PMA.slidebox]],​ [[https://​www.pathomation.com/​pma.control|PMA.control]] etc. 
  
 {{ ::​pmastudio_external_data.jpg?​600 |}} {{ ::​pmastudio_external_data.jpg?​600 |}}
external_data.1668085318.txt.gz · Last modified: 2022/11/10 16:01 by yves