Queryable Items View

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.
Tip

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.

Caution

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

Note

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.