Differences

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

--- external_data [2022/03/25 17:04] – [Consumption and applications] yves
+++ external_data [2023/11/21 16:00] (current) – chris
@@ Line 6: / Line 6: @@
 ==== Configuring data connections ====
+The first step in configuring your external data link to PMA.core is to create the connection to the data source. PMA.core supports connection to the following databases
+  * SQL Server
+  * Oracle
+{{ :external_connection_add.png?600 |}}
+To add an external connection you simply click on the //Add// button in ExternalData page of the administrator front-end UI and then select the type of connection you want. On the next page you will be prompted to add
+  * Name: A name for the new connection to distinguish this connection from others
+  * Connection String: The connection string to the database system including the credentials PMA.core will use to connect
+{{ ::externa_connection_add_page.jpg?600 |}}
 ==== 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 the 'Add' button in the external data source page
 === SQL query ===
-include at least one screenshot here
+{{ ::add_external_data.jpg?600 |}}
+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)
+  * 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
+  * 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 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)
+  * 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.
+As stated an above the **#SLIDE_IDENTIFIER#** part of the query is dependent on the mode selected and the regular expression, if 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:
 === Testing ===
-include at least one screenshot here
+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 |}}
+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 occur, a popup will appear showing the results of the query.
+{{ ::test_query_popup.jpg?400 |}}
 ==== 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.
+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.
-Some screenshots from the SQLServer side on one hand, and the downstream presentation in PMA.studio perhaps?
+{{ ::pmastudio_external_data.jpg?600 |}}
 ==== More background ====