This article gives an example of an advanced validation rule that navigates to parent and child records from a central entity (Customer). If you are struggling to write SemQL for a complex business requirement, this example might give you ideas on how to structure your own validation rule.
Business Background
This is a Customer B2B use case. Here are some relationships between entities in this B2B data model.
- Customers (child) belong to a MasterCustomer (parent)
- MasterCustomers (parent) can have many SubGroups (child). SubGroups are a way to categorize different Customers within 1 MasterCustomer.
- Customers can belong to many SubGroups. And a SubGroup can be related to multiple Customers. This many-to-many relationship is captured using the CustomerGroup join entity.
- There are Relationships (child) between different Customers (parent). For example, Customer Milwaukee Electric Tool Corp. could have a relationship to Home Depot Inc. where the relationship type is "Transactional" and Milwaukee Electric Tool Corp. serves as the "supplier" to Home Depot Inc., which serves as the "customer".
In our earlier example, a MasterCustomer called HOME DEPOT has 2 SubGroups called Home Depot California and Home Depot Texas. These SubGroups drill down to the different Home Depot stores. The SubGroup Home Depot California will have customer records, such as East San Jose #6672, Capitol Expressway #6621, and Hillsdale #1009.
The MasterCustomer HOME DEPOT has all the Home Depot customer location records underneath it, including East San Jose #6672, Capitol Expressway #6621, and Hillsdale #1009.
When we described the earlier example about relationships, imagine there are also relationships between the different Home Depot stores and Milwaukee Electric Tool Corp. - Hayward (which rolls up to the MasterCustomer TECHTRONIC).
Use Case
The business users want to categorize Customers and sort them into different SubGroups. The business only allows customers to join SubGroups if the SubGroups belong to the same MasterCustomer that the customer belongs to.
In our example, this means that East San Jose #6672 and Capitol Expressway #6621 can get grouped together under the SubGroup Home Depot California, because Home Depot California rolls up to the MasterCustomer HOME DEPOT because both customer records roll up to the same MasterCustomer HOME DEPOT.
But East San Jose #6672 and Hillsdale #1009 cannot roll up to the SubGroup Milwaukee Tools West Coast because these Home Depot customer locations don't belong to the MasterCustomer TECHTRONIC.
An exception to the SubGroup membership rule stated in the previous paragraph occurs when there's relationships between customers. Remember we stated that there is a relationship between the Home Depot stores East San Jose #6672, Capitol Expressway #6621, and Hillsdale #1009 and the customer Milwaukee Electric Tool Corp. - Hayward. This relationship allows Milwaukee Electric Tool Corp. - Hayward to join the SubGroup Home Depot California.
Validation Logic
To summarize, customers can only join a SubGroup if the customers in the SubGroup all belong to the same MasterCustomer OR there is a relationship between 2 customers that belong to 2 different Master Customers.
Because of the relationship, we can allow the customer to join the SubGroup, even if the customer belong to a different MasterCustomer.
SemQL Validation Rule
( SubGroup.FID_MasterCustomer = Customer.FID_MasterCustomer
and Customer.FID_MasterCustomer is not null
)
/* Relationship test - at Customer Group */
or ( ANY Customer.FromClient HAVE /* Walked to Customer and down the FromClient Reference - at Relationship */
( ANY ToClient.CustomerGroups HAVE /* Walked to Customers and down to Customer Groups - Customer Group */
(
Customer.ID = ParentRecord.FID_ToClient /* At Customer Group */
and FID_SubGroup = ParentRecord.ParentRecord.FID_SubGroup /* Relationship 2nd ANY - (ParentRecord.ParentRecord - Customer Group 1st ANY)*/
)
)
)