Dataverse table with 2 lookup and 1 formula columns

Task

I have contracts with customers. Each contract may have only one customer assigned.

In Microsoft Dataverse I have two tables with the customer data: existing SAP entities and potential customers that do not exist in SAP and maintained manually.

I should be able to assign a contract to either existing SAP entity or potential customer entity.

I should see an error when trying to assign a contract to two tables at one time, i.e. when existing SAP entity is assigned, the system should not allow me save the changes when trying to assign a potential customer entity at the same time, and vise versa.

I should load customer entity name from existing contracts in one filter dropdown that shall look like this:

Tables

SAP Customer

Keeps data from SAP table LFA1 with existing entities from SAP and field/column `Data JSON' with json data of SAP entity like name, VAT, address, SAP nr, etc.

Potential Customer

Keeps data of a potential customer, entities which do not exist in SAP and field/column `Data JSON' with json data of a custom entity like name, VAT, address, etc.

Contract

Keeps data of contracts with customers. Each contract can be assigned to only one customer entity: either from SAP Customer or Potential Customer tables.

Setup

Table Contract

Fields / Columns

Customer

Formula column that shows json data value either from SAP Customer or Potential Customer tables

• Data type -> Formula
• Format -> Text
• Formula ->

If(
    !IsBlank('SAP Customer Lookup'), 'SAP Customer Lookup'.'Data JSON',
    !IsBlank('Potential Customer Lookup'), 'Potential Customer Lookup'.'Data JSON'
)

The logic of the formula is this:

• Step 1: if SAP Customer Lookup is not empty, use Data JSON column from SAP Customer table
• Step 2: if Potential Customer Lookup is not empty, use Data JSON column from Potential Customer table.
• Step 3: leave blank if Step 1 and Step 2 are not true

function-if reference

SAP Customer Lookup

• Data type -> Lookup

Lookup to table SAP Customer with column Data JSON having json data of a SAP entity.

Potential Customer Lookup

• Data type -> Lookup

Lookup to table Potential Customer with column Data JSON having json data of a potential customer entity.

Business Rules

• Name -> Validate Customer
• Business Rule (Text View)

IF
    `SAP Customer Lookup` contains data AND `Potential Customer Lookup` contains data
THEN
    Show the error message "Value in only one of the two fields can be selected at a time (`SAP Customer Lookup` or `Potential Customer Lookup`)" against field `SAP Customer Lookup`
    Show the error message "Value in only one of the two fields can be selected at a time (`SAP Customer Lookup` or `Potential Customer Lookup`)" against field `Potential Customer Lookup`