Venn Set Queryable Items allow you to specify multiple sets of data you want to merge into one cohesive result set. The sets of data you can include in a Venn Set come from 2 main sources; Entities, History, and Queries themselves. The last one allowing you to include other Venn Sets themselves within a venn set.
Venn Set Queryable Items features
The items you can query in a Venn Set are represented here in a list that easily shows at a glance all the sets of data that you are merging into one set. This also includes how the items are merged together by the type of merge operator. The items you can query come from the following main sources:
Entities This allows you to query all entities in the system, and all their fields, including custom entities and those created through our API.
Audit History This allows you to query all of the audit history associated with any entity. The audit history itself contains the following main properties:
User Modified By
DateTime stamp
ChangeType
Update
Insert
Delete
Property Name
Old Value
New Value
Old Link Value
New Link Value
Queries This allows you to query any public SQL Wrapper in the system, allowing you to incorporate that set of data into your query whether it is created from a Venn Set itself or a hand written SQL query.
Being able to query SQL Wrappers (which every Dataset and Venn Set creates one of anyway to retrieve their data [see here for more info]) allows you to easily build up a library of re-usable queries. These can be used by any user to get results but also to query themselves without having to understand any of your underlying data structure.
Venn Sets are responsible for creating a single set of data by combining multiple set of data together. These multiple set must all have the same structure in order to be merged together correctly. Each queryable item can be filtered, but if you want to create complex data sets to be included in a Venn Sett, it is best to create the complex query first as a Dataset and then to simply include that in the Venn Set itself.
Venn Set Queryable Items Properties
Whenever you create a Venn Set Queryable Item the system pops up a dialog box allowing you to choose the properties of the item you want to query. This has the following properties:
Name
Description
[Required] Queryable Item Type
The type of item you want to query. This can be an Entity, History, or Query.
Queryable Item
The item of the specified type that you want to query
Queryable Item Option
Some Queryable Items contain sub options. Permissions is an example of this. If you select a Permission item to query of the Entity type, this will display a set of options for which entity type permission you want to query
[Required] Name
This is the user friendly name of the queryable item as it will appear in the list of Venn Set Queryable Items.
Set Operator
If this Queryable Item comes after any other in the list, then this specifies the operation to be used to merge this and the prior item data together:
Union Returns distinct values that are returned from the query on the left and right sides of the union operator
Union All Returns all values that are returned from the query on the left and right sides of the union operator
Intersect Returns distinct values that are returned by both the query on the left and right sides of the intersect operator
Except Returns distinct values from the query left of the operator that are not also found on the right query
You can always edit these properties for any Queryable Item by selecting the
Properties command.
Parameters
Whenever you create a new Queryable Item the system allows you the ability to pass parameters to that queryable item from the main parameters defined against the Venn Set itself [see here].
You can always click on the
Parameters command and change these parameters for any Queryable Item. These parameters have the following properties:
Name
Description
[Required] Source Queryable Item Field
A parameter within the global parameters for the Venn Set itself.
[Required] Target Queryable Item Field
This is the property on the selected queryable item that you want to use to join to the global parameter value. This will list all fields against this queryable item, and if it has parameters (is a query queryable item) those will also be listed allowing you to pass values into the parameters as part of the join.
You can add as many parameter joins as you require here, to pass as many global parameters from the Ven Set to the queryable item as you require.
Commands
A Venn Set Queryable Item has the standard set of commands and functions when being viewed from a grid. See our standard grid functionality.
Queryable Items also have the following custom commands:
Properties Opens the popup dialog box allowing you to edit the properties of the Queryable Item such as the type and name. This is described in more detail above
Parameters Opens the popup dialog box allowing you to edit the joins of the Queryable Item to the main parameters for the Venn Set. This is described in more detail above.
