Formulas

The integration of Formulas between V5 Traceability and a customer’s ERP system allows for formulas/recipes/bills of materials to be imported, where they can then be scheduled via work orders to produce batches or products using the V5 Terminal.

More information on how the formulation module in V5 Traceability works, please see our module guide here.

1. Control Center Layout
2. API Links
3. Integration Template
4. Formula Field Guide
   1. Primary Keys
   2. Required Fields
   3. Preferred Fields
   4. Additional Fields
5. Methodology
   1. JSON – Import
   2. JSON – Export
   3. CSV – Import
   4. CSV – Export

In terms of how we can relate Formula details to what we see in Control Center, we will be using the V5 API and Gateway to populate both the upper ‘Formula’ panel to input formula header data, as well as the lower ‘Formula Step’ panel to populate formula step details.

 

Depending on our integration method, we will be making use of the ‘Formula’ and ‘FormulaStep’ endpoints to populate both of these panels and complete our formula setup.

To find out more about the definitions for these database classes please see the following links:

Formula

FormulaStep

The integration template for Formulas can be downloaded here.

Primary Keys are the unique identifier for each table within the V5 API. For Formula, this is:

commodity.code – The underlying commodity code of the formula. The formula/recipe number.

For FormulaStep, the primary keys are:

formula.commodity.code – The same commodity code for the formula itself as described above.

sequence – Used to determine the order of the individual formula steps within a formula setup, i.e. 1, 2, 3 etc.

Other fields that are required by SG to populate a valid Formula line in Control Center are:

For Formula:

type – The type of formula. This should be 0 (for a batch formula) or 1 (for a product formula).

defaultSize – the ‘Base Size’ of the formula in question.

commodity.units.code – The weight unit the formula is to be produced in. If not stated this will create the formula in the database’s default unit of measure.

For FormulaStep:

type – the formula step type.

The step types are as follows:

0 – Weigh
1 – Key Entry
2 – Question
3 – Message
4 – Container Change
5 – Product Component
6 – Product Packaging
7 – Submix
8 – Collation
9 – Bulk Dispesne
10 – Optional
11 – Scan
12 – Revise
13 – Suspend

Preferred fields serve to add more information against the formula in question, and while not required, are useful when it comes to added functionality within V5 Traceability.

For Formula these fields are:

commodity.units.code – The weight unit the formula is to be produced in. If not stated this will create the formula in the database’s default unit of measure.

minimumProducts – The minimum size of the batch that can be produced

maximumProducts – The maximum size of the batch that can be produced.

For FormulaStep these fields are:

sequence – the sequence of the formula step, i.e. what order it should be processed in during production.

commodity.code – the commodity/ingredient to be used for the particular step

targetQty – the target weight for the particular step

unit.code – the UoM of that particular step

question.question– If this step is to be a question or message, then this can be entered using this field.

We can, as we have seen in the primary keys and required fields sections, traverse between these 2 ‘formula’ and ‘formulaStep’ classses, and so can include the above datapoints for use with either class, provided we can traverse to that class successfully.

Additional fields can also be included, such as, for FormulaStep:

upperTol – The upper tolerance for that particular step i.e. the most the weight can be over the target quantity to be acceptable when the formula is being produced.

lowerTol – The lower tolerance for that particular step i.e. the most the weight can be under the target quantity to be acceptable when the formula is being produced.

schedule – Whether the step should be scheduled when the formula is produced. Useful for steps that are submixes and need to be produced at the same time as a master formula.

As discussed above, we could still make use of these datapoints when using the ‘formula’ endpoint, simply by traversing to the ‘formulaStep’ class by adding ‘formulaStep’ to each of the above.

Depending on our integration methodology for formulas, we would generally use different endpoints; For API integration we would generally use ‘Formula’, and for CSV ‘FormulaStep’. We can take a look at both of these now and see why this is the case.

Formula:

We can make use of the ‘Formula’ endpoint to create new formulas for production in V5 Traceability. As we will see, using this endpoint easily allows us to capture both the formula header and step info in 1 file.

‘Formula’ import Endpoint/URI –

http://host:port/V5-API/api/integrate/import/formula

Endpoint Description

For this sample import using the ‘Formula’ endpoint, we can structure a basic import file for a single formula with 2 formula steps as below:

This sample JSON file can be downloaded here.

Using the API manual, we can plot out the paths that we are using to traverse to the various classes that we need to use for this import. In the ‘Formula’ class we can see that all we are using here is the ‘type’ and ‘productionLocation’. From here we need to define our formula commodity information, which we can do by traversing from ‘Formula’ to ‘Commodity’. Here we can enter the commodity information for the formula itself (its code, description etc.).

To then define the formula steps, we first need to traverse to the ‘FormulaStep’ class, where we can define the step type, target quantity, tolerances etc. From here, we would again traverse to the ‘Commodity’ class, except that here, because of the route we have taken to the class, we will be defining the commodity information for the ingredient to be used for a specific formula step, rather than the header information that we defined already above.

From here we would then traverse from both of the ‘Commodity’ classes to arrive at the ‘WeightUnit’ class, where we can define the UoM for both the formula itself, as well as each individual step. So even though we are using the same class for this, we have reached it in 2 different ways, which is reflected in the definitions we can see (commodity.units.code vs formulaStep.commodity.units.code), allowing us to use different UoMs if desired.

We can see a summary of the pathfinding required to produce the above JSON file here:

We can build up the rest of our required datapoints using a similar pathfinding method.

If we run the JSON file above, we will see that this new formula, along with its steps, is now visible in the ‘Formulas’ tab in Control Center.

FormulaStep:

‘FormulaStep’ import Endpoint/URI –

http://host:port/V5-API/api/integrate/import/formula_step

Endpoint Description

For this sample import using the ‘FormulaStep’ endpoint, we will look at importing the same formula that we did above, but we will just change its name. Using this endpoint we can structure this file as below:

We can also make use of the ‘FormulaStep’ endpoint to create new formulas for production in V5 Traceability. This would work in largely the same way as using ‘Formula’ above, but we would effectively have to traverse the database classes in reverse, so instead of going from ‘Formula’ to ‘FormulaStep’, we would go from ‘FormulaStep’ to ‘Formula’. We can see how this would work below:

This sample JSON file can be downloaded here.

As we can see here, we are entering the same information in terms of the steps, and just changing the formula name. Note here the traversal from the ‘formulaStep’ class to the ‘formula’ class, and the fact that this information is required to be nested for each formula step.

If we now run this example JSON file, our duplicate formula will now be created with the same settings as we saw when using the ‘Formula’ endpoint.

In terms of receiving files relating to formulas/recipes back from the V5 API, we are not really tracking any consumption data here, so instead we will simply be exporting a formula’s configuration/setup. There are still a few options for this if we check the available ‘IntegrationExport’ services:

Formula:

Export Endpoint/URI –

http://host:port/V5-API/api/integrate/export/formula/{code}

Endpoint Description

This endpoint will export a JSON file with all related information for a specific formula. If we run this GET request for the formula we created via the ‘Formula’ endpoint, we will get a return file that looks something like this:

An example JSON file can be downloaded here.

All Formulas:

Export Endpoint/URI –

http://host:port/V5-API/api/integrate/export/formulas

Endpoint Description

This endpoint will export a JSON file that includes a list of all formulas contained within the system. Note that this will only return the header data for each formula. To retrieve formula steps, see below.

A sample JSON files of this type can be downloaded here.

Formula Step:

Export Endpoint/URI –

http://host:port/V5-API/api/integrate/export/formula_step/{code}/{sequence}

Endpoint Description

This endpoint can be configured to return data for a specific step of a specific formula. We would specify which step we want to retrieve by using its sequence number. If we hit this endpoint to retrieve step 1 of the first example formula we created above, we will get a return that looks something like this:

This example JSON file can be downloaded here.

Formula Steps:

Export Endpoint/URI –

http://host:port/V5-API/api/integrate/export/formula_steps/{all}

Endpoint Description

This endpoint provides a list of all formula steps within the system. We can change the return by using either ‘/true’ or ‘/false’ in place of ‘/{all}’ in the URI above. ‘True’ will export a list of all steps in the system, whereas ‘false’ will return a list of steps not marked as already exported.

This would look very similar in layout to the example above, just with more steps present. An example of a ‘true’ export of this type can be downloaded here.

We have seen in the JSON examples above that we can use both the ‘formula’ and ‘formulaStep’ endpoints for that particular methodology. However, for CSV imports we will only use the ‘formulaStep’ endpoint.

Header/column definition filename: “formulaStep.csvh”

Completed header files should be placed in: “<installdir>\SG Control Center\gateway\import\column_defs”

Import CSV filename: “formulaStep-datetime.csv”

CSVs for import should be placed in: “<installdir>\SG Control Center\gateway\import”

Header File:

Header files will generally be compiled by SG Systems prior to CSV integrations taking place, but we can see a basic example of one we can use for jobs below:

For further information on how we structure these files, please see the main integration page. This sample header file can be downloaded here.

CSV Import File:

We can then use the defined order of data points in the header to structure our import file. SG Systems can supply a template file for this, listing the data point for each column to make things clearer when populating the list. Note that this first row can be ignored by Control Center and so can be kept in the file when submitting for import.

Again we will create a duplicate formula of the 2 we created via the JSON method above. An example csv import file for a formula could look something like this:

This sample import file can be downloaded here.

With the import complete, we can see that this additional formula has been added to Control Center with the same steps as the formulas that we created using JSON imports above.

For our CSV exports, we will just make use of the same ‘formulaStep’ header file/endpoint as above for the imports. This can be downloaded here.

Header/column definition filename: “formulaStep.csvh”

Completed header files should be placed in: “<installdir>\SG Control Center\gateway\export\order”

Export CSV filename: “FormulaStep-datetime.csv”

CSVs for exports will be generated in: “<installdir>\SG Control Center\gateway\export”

This is the only way to get formula data back from V5 Traceability in the form of a CSV.

This export would also need to be enabled in the Control Center’s Gateway section. We can choose here to export ‘Formulas’.

Header File:

For this example, we can use the header previously used for import for the export. Additional data points can be added to this if desired.

CSV Export File:

Enabling this export using the previously used header would give an export file for our 3 new formulas that would look like this: