Lookups
Query columns can have one or more formatters associated with them. This is not at all user friendly as it was always intended to have editor support. Usually you’ll only need one.
The most likely case where you might need more than one is where data is currently stored in the DB in json – in that case you might want to use the json query to extract something and then use a ‘FormatString’ on the result.
There are a list of ‘global’ lookups that can be applied to any column. There are also some column-specific lookups - Some of them are really specific, or just straight up hacks.
Global.FormatString lookup
Example:
<Column name="FixedValue" source="Id">
<Format lookup="@Tables.Global.FormatString">
<ParameterSelect name="Template" source="@Parameters.Fixed Value" kind="Text" />
<ParameterSelect name="Arg1" source="@Columns.Surname" kind="Object" />
</Format>
</Column>
The first parameter is required, and then you can have up to 3 ‘arguments’. A parameter source can be a parameter, or the raw selected value of other columns. The template value (usually setup as a string parameter) could look like:
Value of ‘this’ column: {0} Value of Arg1: {1} Etc.
Global.Query lookup
Example:
<Column name="Some field Value" source="@Tables.Some Table Value">
<First />
<Format>
<Query>$[?(@.LabelCode == 260905004)].Value</Query>
</Format>
</Column>
</Columns>
Query raw Json using JsonPath syntax (newtonsoft flavour: https://www.newtonsoft.com/json/help/html/QueryJsonSelectTokenJsonPath.htm).
Grouping
Columns can be specified as ‘grouped’ by including them in the ‘order’ element, and specifying the ‘grouped’ attribute, e.g.
<?xml version="1.0" encoding="utf-16"?>
<Query xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" table="Patient Demographics" xmlns="http://meddbase.com/DynamicQuery.xsd">
<Columns>
<Column name="Id" source="Id">
<Count />
</Column>
<Column name="Insurer Id" source="Insurer Id" />
<Column name="Insurer Demographics Full Name" source="Insurer Demographics.Full Name">
<First />
</Column>
</Columns>
<Order>
<Ascending source="@Columns.Insurer Id" group="true" />
</Order>
</Query>
Once a column is grouped, all non-grouped columns require an aggregate. For grouped columns that already required an aggregate (1:n relations or table aliases) the grouping is performed on the aggregated result.