---
title: "Look up Specifications from Another Table when Creating Quality Inspection Tests"
app: "Quality Inspector"
section: "How-to guides"
last_updated: "2024-11-26"
url: "https://insight-works.com/kb/quality-inspector/look-up-specifications-from-another-table-when-creating-quality-inspection-tests/"
---

# Look up Specifications from Another Table when Creating Quality Inspection Tests

The following guides you through an example of using product specifications from another table as part of a pass condition when creating Quality Inspection Tests. These specifications are target values, and you can use Quality Inspector either directly or reference these values (plus or minus a percentage).

For this example, the following must be reached:

- Ash must match the target specification exactly.
- Nitrogen can be +/- 2% of the target specification.
- Salt can be 1% lower and 3% higher than the target specification.

The custom _Example Specification_ table contains example target values of specifications.

| **Version** | **Target Protein Content %** | **Target Nitrogen %** | **Target Fatty Substances %** | **Target Ash %** | **Target Fiber %** | **Target Salt %** |
| --- | --- | --- | --- | --- | --- | --- |
| CHEESE1 | 23.00 | 3.70 | 33.00 | 4.00 | 0.00 | 2.50 |
| CHEESE2 | 20.00 | 3.70 | 34.00 | 4.00 | 0.00 | 2.20 |
| CHEESE3 | 22.00 | 3.10 | 32.00 | 2.00 | 0.00 | 2.30 |
| CHKPEA | 9.00 | 1.30 | 2.50 | 3.00 | 8.00 | 1.50 |
| WWFLR | 14.00 | 2.20 | 2.00 | 1.50 | 12.00 | 0.10 |

The table definition is:

```
table 50620 "Example Specification"

{

    LookupPageId = "Example Specification";

    DrillDownPageId = "Example Specification";

    fields

    {

        field(1; Version; Code[20])

        {

            NotBlank = true;

            ToolTip = 'The version of the specification.';

        }

        field(2; "Target Protein Content Pct."; Decimal)

        {

            Caption = 'Target Protein Content %';

            ToolTip = 'Target Protein Content %';

        }

        field(3; "Target Nitrogen Pct."; Decimal)

        {

            Caption = 'Target Nitrogen %';

            ToolTip = 'Target Nitrogen %';

        }

        field(4; "Target Fatty Subst. Pct."; Decimal)

        {

            Caption = 'Target Fatty Substances %';

            ToolTip = 'Target Fatty Substances %';

        }

        field(5; "Target Ash Pct."; Decimal)

        {

            Caption = 'Target Ash %';

            ToolTip = 'Target Ash %';

        }

        field(6; "Target Fiber Pct."; Decimal)

        {

            Caption = 'Target Fiber %';

            ToolTip = 'Target Fiber %';

        }

        field(7; "Target Salt Pct."; Decimal)

        {

            Caption = 'Target Salt %';

            ToolTip = 'Target Salt %';

        }

    }

    keys

    {

        key(PK; Version) { }

    }

}
```

The item “Cheese” references one of the specifications, and the field is _Example Specification Version_.

For this example, we are going to set the _Example Specification…_ field to “CHEESE1” for our “CHEESE” example item. This allows the item to change specifications over time to the custom specification table.

The rest of the Item Card’s information should be as follows:

- No.: CHEESE
- Description: Cheese
- Type: Inventory
- Example Specification…: CHEESE1

The table extension with this custom field is:

```
tableextension 50620 "Example Specification Item" extends Item

{

    fields

    {

        field(50620; "Example Specification Version"; Code[20])

        {

            TableRelation = "Example Specification".Version;

            ToolTip = 'Example Specification Version';

        }

    }

}
```

This example uses three fields in its Quality Inspection Template. Create three fields (ash nitrogen, and salt).  These all have percentages with an allowable value of between 0 and 100.

 Note: Allowable values are not the pass conditions; they define the technically possible values. In the case of percentages, this is 0% through 100%.

| Code | Description | Field Type | Allowable Values | Default Value |
| --- | --- | --- | --- | --- |
| EXASH | example ash | Decimal | 0..100 |  |
| EXNITROGEN | example nitrogen | Decimal | 0..100 |  |
| EXSALT | example salt | Decimal | 0..100 |  |

Create a quality inspection template for the food product using the three fields above (ash, nitrogen, salt).

Input into the following fields:

- Code: FOODEXAMPLE
- Description: Food example with lookups

Select **Test Generation Rules** and create a test generation rule to match the food products.

For this example, use a purchase line and filter to match the Cheese item.

| **Description** | **Table** | **Condition Filter** | **Item Filter** | **Attribute Filter** |
| --- | --- | --- | --- | --- |
| Food example with lookups | Purchase Line |  | WHERE(No.=FILTER(CHEESE\*)) |  |

### Ash condition

For the Ash condition, the requirement is that it must match the target specification exactly. The target specification is in the _Example Specification_table. You can set the target specification as numbers in the pass condition.

To reduce configuration and duplication of configuration in the system, leverage the following features:

- Item lookups.
- Custom table lookups.

In the template:

1. Select **Add Field(s)** to This Template.
2. Select **New Field**.
3. Input “Example Target Ash” in the _Description_ field.
4. Select “Advanced Configuration” in the _What type of data is it?_ field.
5. Select Next.
6. Select “Numeric Expression” in the _Field Type_ field.
7. Select the **…** (Assist Edit) button in the _Expression Formula_ field.
8. Enter the following formula:

[Lookup(Example Specification;Target Ash Pct.;Version=[Item:Example Specification Version])]

1. Select **Close.**

Note: The expression formula updates in the wizard, and there is a new calculated formula added to the template.

1. Create a new test.

The pass condition for salt on the new Quality Inspection Test is now “4”.

Note: Breaking down how it evaluated to “4”:

The item is “CHEESE”. On the _Cheese_ item card, the _Example Specification Version_ field is set to “CHEESE1”. This causes [Item:Example Specification Version] to evaluate to “CHEESE1”.

The formula would then change from:

 [Lookup(Example Specification;Target Ash Pct.;Version=[Item:Example Specification Version])]

To:

 [Lookup(Example Specification;Target Ash Pct.;Version=CHEESE1)]

The formula is now looking up the _Target Ash Pct._ field from the _Example Specification_ table where the _Version_ field is “CHEESE1”.

This evaluates to “4”, making the pass condition “4”.

#### Create a Quality Inspection Test to test configuration so far

The parameters on the _Create Quality Inspection_ page read as:

- Template Code: FOODEXAMPLE
- Source: PURCHTOTEST
- Choose Record: Purchase Line: Choose a purchase order line in your system that is purchasing a cheese item for a given lot.
- Item No.: CHEESE
- Serial No.: [Blank]
- Lot No.: WENSLEYDALE001
- Package No.: [Blank]

The pass conditions on the new _Quality Inspection Test_for (after completing the corresponding sections):

- Ash: 4
- Nitrogen: 1.7..5.7
- Salt: 1.5..5.5

### Nitrogen condition

The next requirement to meet is “Nitrogen can be +/- 2% of the target specification”.

To do this, use a combination of:

- Embedded formulas
- Item lookups
- Table lookups

In the template:

1. Select **Add Field(s)** to This Template.
2. Select **New Field**.
3. Input “Example Target Nitrogen” in the _Description_ field.
4. Select “Advanced Configuration” in the _What type of data is it?_ field.
5. Select **Next.**
6. Select “Numeric Expression” in the _Field Type_ field.
7. Select the **…** (Assist Edit) button in the _Expression Formula_ field.
8. Enter the following formula:

{[Lookup(Example Specification;Target Nitrogen Pct.;Version=[Item:Example Specification Version])]-2}..{[Lookup(Example Specification;Target Nitrogen Pct.;Version=[Item:Example Specification Version])]+2}

1. Select Close.

Note: The expression formula updates in the wizard, and there is a new calculated formula added to the template.

1. Create a new test.

The pass condition for salt on the new Quality Inspection Test is now “1.7..5.7”.

Note: Breaking down how it evaluated to "1.7..5.7":

The item is "CHEESE". On the _Cheese_ item card, the _Example Specification Version_ field is set to "CHEESE1". This causes [Item:Example Specification Version] to evaluate to "CHEESE1".

The formula would then change from:

..

To:

..

The first embedded expression gets analyzed: 

The formula is now looking up the _Target Nitrogen Pct_ field from the _Example Specification_ table where the _Version_ field is "CHEESE1".

This evaluates to "3.7".

The first embedded expression then becomes: 

And evaluates to: 1.7

The pass condition is now: 1.7..

The second embedded expression gets analyzed: 

Like the first embedded expression, the formule is now looking up the field _Target Nitrogen Pct_ field from the _Example Specification_ table where the _Version_ field is "CHEESE1".

This evaluates to "3.7".

The second embedded expression then becomes:

And evaluates to: 5.7

The pass condition for Nitrogen is now:

1.7..5.7

 Note: Return to the _Create a Quality Inspection Test to test configuration so far_ section to test the configuration.

### Salt condition

The final requirement to meet is “Salt can be 1% lower and 3% higher from the target specification”. This is like the previous requirement for Nitrogen, but the approach is different and instead uses the addition of another calculated field.

To do this, use a combination of:

- Embedded formulas
- Item lookups
- Table lookups.
- Calculated Field

In the template:

1. Select **Add Field(s) to This Template**.
2. Select **New Field**.
3. Input “Example Target Salt” in the _Description_ field.
4. Select “Advanced Configuration” in the _What type of data is it?_field.
5. Select **Next**.
6. Select “Numeric Expression” in the _Field Typ_field.
7. Select the **…** (Assist Edit) button in the _Expression Formula_field.
8. Enter the following formula:

[Lookup(Example Specification;Target Salt Pct.;Version=[Item:Example Specification Version])]

1. Select **Close**.

 Note: The expression formula updates in the wizard, and there is a new calculated formula added to the template.

1. Select the **…** (Assist Edit) button on the pass condition for “EXSALT”.
2. Input the following formula:

{[EXAMPLETARGETSALT]-1}..{[EXAMPLETARGETSALT]+3}

1. Create a new test.

The pass condition for salt on the new _Quality Inspection Test_ is now “1.5..5.5”.

Note: Breaking down how it evaluated to "1.5..5.5":

"EXAMPLETARGETSALT" evaluates to 2.5 via a calculated expression.

The formula: ..

The first embedded expression gets analyzed:

The first embedded expression then becomes:

And evaluates to: 1.5

The pass condition is now: 1.5..

The second embedded expression gets analyzed:

The second embedded expression then becomes:

And evaluates to: 5.5

The pass condition for salt is now:

1.5..5.5

 Note: Return to the _Create a Quality Inspection Test to test configuration so far_ section to test the configuration.

You have now used several features including item lookups, custom table lookups, embedded formulas, and calculated fields to allow a variety of automatically changing pass conditions.
