Scoping

Document Overview:

The purpose of this document is to provide a comprehensive guide for integrating the data collection, lookup, and calculation processes from various Excel sheets into the ERP system. This integration will simulate the existing processes where data is manually input, automatically fetched from other sheets, or calculated based on provided formulas.

The key objectives of this integration are:

1. To ensure that all necessary data from each sheet is captured and accurately transferred into the ERP system.

2. To implement automated lookups that retrieve data from other sheets based on a unique key (like Sample Number).

3. To incorporate all necessary calculations, ensuring the ERP system reflects the same outcomes as the Excel sheets.

The following instructions detail how the development team should approach the implementation, manage data lookups, and handle calculations.

Instructions for Development Team

1. General Steps for Implementation:

1. Identify the Data: For each sheet, ensure that all columns, including manual inputs, lookups, and calculated fields, are properly mapped to the corresponding fields in the ERP system.

2. Set up Manual Input Fields: For all columns that require manual input, create corresponding fields in the ERP system where users can enter data.

3. Implement Lookup Logic: For columns that refer to data in other sheets, use database queries or lookup functions to fetch the required data based on the Sample Number or other unique identifiers.

4. Perform Calculations: For columns that involve calculations, replicate the formulas in the ERP system, ensuring the calculations are triggered automatically when the required input data is available.

5. Handle Errors and Data Validation: Implement error handling mechanisms to ensure that invalid or missing data is handled gracefully. If lookups fail, provide default values or notify the user.

6. Ensure Real-Time Updates: Ensure that the lookups and calculations are dynamic, meaning any changes in input data should trigger immediate updates to the corresponding fields.

2. Column-Specific Instructions by Sheet

Sheet 1: Sample List

Columns:

• TAT

• Date of Sampling

• Date Analysed

• Sample Number

• Sample Description

• Batch/Order number

• Certificate number

• Person delivering the sample

• Sample Type

• Weighbridge Description

• % Moisture (H2O @ 105°C)

• % >150µm

• % <150µm

• %Cl (Titration)

• LOI of +150um

• LOI of -150um

• % Loss on ignition @ 900°C

• % ATH (determined from %LOI)

• SG or Bulk Density (g/mL)

• pH (Value)

• Solids in liquid sample (%)

• Na2O, MgO, Al2O3, SiO2, P2O5, SO3, Cl, K2O, CaO, TiO2, V2O5, Cr2O3, MnO, Fe2O3, ZnO, PbO, Na, Mg, Al, Si, P, S, Cl, K, Ca, Ti, V, Cr, Mn, Fe, Zn, Ni, As, Se, Cd, Sb, Hg, Pb

Instructions:

1. Manual Input:

   • Allow manual input for TAT, Date of Sampling, Sample Number, Sample Description, etc.

2. Lookups:

   • Implement lookups for columns like Date Analysed, Weighbridge Description, % Moisture, % >150µm, and others. Use the Sample Number as the key for fetching the required data from other sheets (e.g., XRF-PP, Weighbridge, H2O, 150, etc.).

3. Calculations:

   • TAT: Calculate the turnaround time as the difference between Date Analysed and Date of Sampling.

   • % ATH: Calculate based on the formula: ((K3 + Q3) / 34.6) * 100.

   • % Loss on Ignition: Implement formulas to calculate loss on ignition using the corresponding mass values.

Sheet 2: Spectron Results

Columns:

• Sample ID

• Date

• SiO2

• Al2O3

• Fe2O3

• MgO

• CaO

• K2O

• TiO2

• V2O5

• MnO

• P2O5

• SO3

• Cr2O3

• Cl

Instructions:

1. Manual Input:

   • Allow manual input for columns like Sample ID and Date.

2. Lookups:

   • Implement lookups to fetch elemental composition data (e.g., SiO2, Al2O3) from other sheets based on the Sample ID.

3. Calculations:

Total Elemental Composition: Sum all elemental composition values to calculate the total.

Sheet 3: H2O

Columns:

• Sample Number

• Weight of beaker (m0)

• Weight of sample (m1)

• Weight of dried sample and beaker (m2)

• Weight of Sample and Beaker (m0 + m1)

• Moisture @ 105°C

• % Moisture (H2O @ 105)

Instructions:

1. Manual Input:

   • Enable manual input for Sample Number, Weight of beaker, Weight of sample, etc.

2. Calculations:

Weight of Sample and Beaker (m0 + m1): Sum m0 and m1.

% Moisture: Calculate as (Moisture / Weight of dried sample) * 100.

Sheet 4: LOI (Loss on Ignition)

Columns:

• Sample Number

• Mass of Sample (+150um)

• Mass of Cup + Sample after Calcining (+150um)

• % LOI of +150um

• % LOI of -150um

• % Loss on Ignition @ 900°C

Instructions:

1. Manual Input:

   • Allow manual input for Sample Number, Mass of Sample, etc.

2. Lookups:

Fetch % LOI values from the Sample List sheet using Sample Number as the key.

3. Calculations:

% Loss on Ignition @ 900°C: Calculate using the formula (Mass of Sample - Mass after 

Sheet 5: Liquid Samples

Columns:

• Sample Number

• SG or Bulk Density (g/mL)

• pH (Value)

• Mass of Filter Paper (g)

• Mass of Filter Paper after drying (g)

• Solids in Liquid Sample (g)

• Solids in Liquid Sample (%)

Instructions:

1. Manual Input:

   • Allow manual input for Sample Number, SG or Bulk Density, pH, Mass of Filter Paper, etc.

2. Calculations:

Solids in Liquid Sample (g): Subtract Mass of Filter Paper from Mass of Filter Paper after drying.
Solids in Liquid Sample (%): Calculate as (Solids / Beginning Mass of Liquid Sample) * 100.

Sheet 6: XRF-Liquid Samples

Columns:

• Sample ID

• Time

• Na

• Mg

• Al

• Si

• P

• S

• Cl

• K

• Ca

• Ti

• V

• Cr

• Mn

• Fe

• Zn

• Pb

• % Solids

• % Liquid

Instructions:

1. Manual Input:

   • Allow manual input for Sample ID and Time.

2. Lookups:

Fetch elemental composition data (e.g., Na, Mg, Al, etc.) from the Spectron Results sheet based on Sample ID.

3. Calculations:

% Solids: Sum the elemental composition values (e.g., Na, Mg, Al, Si, etc.).

% Liquid: Calculate as 100 - % Solids.

Sheet 7: Weighbridge

Columns:

• Truck Number

• Client

• Haulier

• Destination

• Product

• Order

• Date In

• Time In

• 1st Mass

• Operator In

• Status

• PCID1

• Date Out

• Time Out

• 2nd Mass

• Operator Out

• Status2

• PCID2

• Sample ID

• Sample Description from Lab

• Net Mass

Instructions:

1. Manual Input:

   • Allow manual input for fields such as Truck Number, Client, Haulier, Product, 1st Mass, etc.

2. Lookups:

   • None required for this sheet.

3. Calculations:

Net Mass: Calculate as 2nd Mass - 1st Mass.

Sheet 8: Titration

Columns:

• Sample ID

• Molarity of (NH4)SCN

• Vi (Initial Volume)

• Vf1 (Final Volume)

• Vtotal (Total Volume)

• Mass of Sample

• Unreacted Ag (mol)

• Reacted Ag (mol) = Reacted Cl (mol)

• Chloride in Sample (g)

Instructions:

1. Manual Input:

   • Allow manual input for Sample ID, Molarity, Vi (Initial Volume), Vf1 (Final Volume), Vtotal (Total Volume), and Mass of Sample.

2. Calculations:

Unreacted Ag (mol): Calculate as Vtotal - Vf1.

Reacted Ag (mol) = Reacted Cl (mol): Calculate as Molarity * (Vi - Vf1).

Chloride in Sample (g): Calculate as Reacted Cl (mol) * Molecular Weight of Cl.

Data Validation and Error Handling

1. Input Validation:

   • For every manual input field, ensure proper data validation is in place:

     • Dates: Validate that date fields are in the correct format (e.g., YYYY-MM-DD).

     • Numeric Fields: Ensure numeric fields such as masses, volumes, and percentages only accept valid numbers.

     • Required Fields: Ensure mandatory fields are filled out before submission.

2. Error Handling:

For lookup failures (e.g., missing Sample Number or Sample ID), return a default value like 0 or display an error message to prompt the user to enter the missing data.

3. Real-Time Updates:

   • Ensure that any change in a manually entered field automatically triggers recalculations for dependent fields (e.g., changing 1st Mass should update Net Mass).

Testing and Simulation

1. Manual Input Testing:

   • Verify that all manual input fields in the ERP system correspond correctly to the columns in the original Excel sheets.

   • Test for proper validation and error handling for all fields (e.g., ensure numeric fields reject non-numeric data).

2. Lookup Functionality Testing:

   • Test the lookup functionality for every field that references other sheets (e.g., retrieving Weighbridge Description, Elemental Composition, etc.).

   • Ensure the system dynamically retrieves and updates data based on the key field (e.g., Sample ID or Sample Number).

3. Calculation Testing:

   • Test all calculation fields to ensure they are calculated correctly based on the formulas outlined in this document.

   • Ensure calculations are re-triggered when input values change (e.g., updating Mass fields should update the corresponding calculation results).

Conclusion

This document provides a comprehensive guide for integrating the Excel-based processes into your ERP system. The instructions provided include handling manual inputs, performing data lookups, managing calculations, and ensuring data validation and error handling. By following this guide, the development team can implement the required functionality seamlessly, ensuring that the system replicates the Excel processes accurately.

Technical Specification for ERP System Integration

Objective:

To integrate the data entry, lookups, and calculation processes described in the provided sample sheets into the ERP system, ensuring proper data flow and automation.

1. Sheet: Sample List

Manual Input Columns:

• TAT

• Date of Sampling

• Sample Number

• Sample Description

• Batch/Order number

• Certificate number

• Person delivering the sample

• Sample Type

• Weighbridge Description

• pH (Value)

• Solids in liquid sample (%)

• Na2O

• MgO

• Al2O3 (total)

• SiO2

• P2O5

• SO3

• Cl

• K2O

• CaO

• TiO2

• V2O5

• Cr2O3

• MnO

• Fe2O3

• ZnO

• PbO

• Na

• Mg

• Al

• Si

• P

• S

• Cl

• K

• Ca

• Ti

• V

• Cr

• Mn

• Fe

• Zn

• Ni

• As

• Se

• Cd

• Sb

• Hg

• Pb

Reference Columns:

• Date Analysed
  Formula: =IF(D3="","",IFERROR(XLOOKUP(D3,'XRF-PP'!A3:A999,'XRF-PP'!B3:B999),""))
  Instruction: Fetch Date Analysed from XRF-PP sheet based on Sample Number.

• Weighbridge Description
  Formula: =XLOOKUP(D3,Weighbridge!$Y$2:$Y$427,Weighbridge!$E$2:$E$427,"")
  Instruction: Fetch Weighbridge Description from Weighbridge sheet using Sample Number.

• % Moisture (H2O @ 105°C)
  Formula: =XLOOKUP(D3,H2O!$A$2:$A$994,H2O!$H$2:$H$994)
  Instruction: Retrieve percentage moisture from H2O sheet.

• % >150µm
  Formula: =XLOOKUP(D3,'150'!$A$2:$A$999,'150'!$D$2:$D$999,"")
  Instruction: Retrieve percentage particles >150µm from 150 sheet.

• % <150µm
  Formula: =XLOOKUP(D3,'150'!$A$2:$A$999,'150'!$E$2:$E$999,"")
  Instruction: Retrieve percentage particles <150µm from 150 sheet.

• %Cl (Titration)
  Formula: =IFERROR(XLOOKUP(D3,Titration!$A$3:$A$991,Titration!$S$3:$S$991,""),"")
  Instruction: Fetch chloride percentage from Titration sheet.

• LOI of +150um
  Formula: =XLOOKUP(D3,LOI!$A$2:$A$994,LOI!$I$2:$I$994)
  Instruction: Fetch LOI value for particles >150µm from LOI sheet.

• LOI of -150um
  Formula: =XLOOKUP(D3,LOI!$A$2:$A$994,LOI!$J$2:$J$994)
  Instruction: Fetch LOI value for particles <150µm from LOI sheet.

Calculation Columns:

• TAT
  Formula: =IF(D3="","",IFERROR((C3-B3),""))
  Instruction: Calculate Turnaround Time (TAT) as the difference between Date Analysed and Date of Sampling.

• % ATH (determined from %LOI)
  Formula: =((K3+Q3)/34.6)*100
  Instruction: Calculate ATH percentage based on LOI values.

2. Sheet: Spectron Results

Manual Input Columns:

• Column1

• Sample ID

• Column12

• Column2

Reference Columns:

• SiO2
  Formula: =XLOOKUP(Sample ID, 'XRF-PP'!A:A, 'XRF-PP'!B:B)
  Instruction: Fetch SiO2 value from XRF-PP sheet.

• Al-Al2O3
  Formula: =XLOOKUP(Sample ID, 'XRF-PP'!A:A, 'XRF-PP'!C:C)
  Instruction: Fetch Al-Al2O3 value from XRF-PP sheet.

Calculation Columns:

• Total Elemental Composition
  Formula: =SUM(SiO2, Al-Al2O3, Fe2O3, etc.)
  Instruction: Calculate total elemental composition by summing individual elements.

3. Sheet: H2O

Manual Input Columns:

• Sample Number

• Weight of beaker (m0)

• Weight of sample (m1)

• Weight of Dried sample and beaker (m2)

Reference Columns:

• Weight of Sample And Beaker (m0+m1)
  Formula: =Weight of beaker + Weight of sample
  Instruction: Calculate the combined weight of the sample and beaker.

Calculation Columns:

• Moisture @ 105°C
  Formula: =Weight of Sample And Beaker - Weight of Dried sample and beaker
  Instruction: Calculate moisture at 105°C.

• % Moisture (H2O @ 105)
  Formula: =Moisture / Weight of dried sample * 100
  Instruction: Calculate percentage moisture.

4. Sheet: 150

Manual Input Columns:

• Sample Number

• <150µm [g]

• >150µm [g]

Reference Columns:

• % <150µm
  Formula: =XLOOKUP(Sample Number, 'XRF-PP'!A:A, 'XRF-PP'!D:D)
  Instruction: Fetch percentage of particles <150µm from XRF-PP.

• % >150µm
  Formula: =XLOOKUP(Sample Number, 'XRF-PP'!A:A, 'XRF-PP'!E:E)
  Instruction: Fetch percentage of particles >150µm from XRF-PP.

Calculation Columns:

• Total Percentage of Particles
  Formula: =SUM(% <150µm, % >150µm)
  Instruction: Calculate total particle distribution by summing the percentages.

5. Sheet: XRF-PP

Manual Input Columns:

• Sample ID

• Time

Reference Columns:

• Cl from Titration
  Formula: =XLOOKUP(Sample ID, 'Titration'!A:A, 'Titration'!B:B)
  Instruction: Fetch chloride from Titration.

• Elemental Composition Columns:
  Na2O, MgO, Al2O3, SiO2, P2O5, SO3, Cl, K2O, CaO, TiO2, V2O5, Cr2O3, MnO, Fe2O3, ZnO, PbO
  Formula: =XLOOKUP(Sample ID, 'Spectron Results'!A:A, 'Spectron Results'!B:B)
  Instruction: Fetch elemental composition from Spectron Results.

Calculation Columns:

• SUM
  Formula: =SUM(Na2O, MgO, Al2O3, SiO2, etc.)
  Instruction: Calculate total elemental composition.

• LOI
  Formula: =SUM(Al2O3 (>150), Al2O3 (total))
  Instruction: Calculate Loss on Ignition (LOI).

6. Sheet: Titration (Continued)

Manual Input Columns:

• Vi (Initial Volume)

• Vf1 (Final Volume)

• Vtotal (Total Volume)

• Mass of Sample

Reference Columns:

• Unreacted Ag (mol)
  Formula: =Vtotal - Vf1
  Instruction: Calculate unreacted silver (Ag) by subtracting final volume (Vf1) from total volume (Vtotal).

Calculation Columns:

• Reacted Ag (mol) = Reacted Cl (mol)
  Formula: =Molarity * (Vi - Vf1)
  Instruction: Calculate the moles of reacted silver or chloride by multiplying the molarity by the difference in volumes.

• Chloride in Sample (g)
  Formula: =Reacted Cl (mol) * Molecular Weight of Cl
  Instruction: Calculate the chloride mass by multiplying the moles of reacted chloride by the molecular weight of chloride.

7. Sheet: LOI (Loss on Ignition)

Manual Input Columns:

• Sample Number

• Temperature

• Mass of Cup (+150um)

• Mass of Sample (+150um)

• Mass of Cup + Sample after Calcining (+150um)

• Mass of Cup (-150um)

• Mass of Sample (-150um)

• Mass after Calcining (-150um)

Reference Columns:

• % LOI of +150um
  Formula: =XLOOKUP(Sample Number, 'Sample List'!A:A, 'Sample List'!B:B)
  Instruction: Fetch the LOI value for particles larger than 150µm from the Sample List.

• % LOI of -150um
  Formula: =XLOOKUP(Sample Number, 'Sample List'!A:A, 'Sample List'!C:C)
  Instruction: Fetch the LOI value for particles smaller than 150µm from the Sample List.

Calculation Columns:

• % Loss on Ignition @ 900°C
  Formula: =((Mass of Sample (+150um) - Mass of Cup + Sample after Calcining (+150um)) / Mass of Sample (+150um)) * 100
  Instruction: Calculate the percentage of material lost after calcining (heating) at 900°C.

8. Sheet: Liquid Samples

Manual Input Columns:

• Sample Number

• SG or Bulk Density (g/mL)

• pH (Value)

• Temp (°C)

• Mass of Sample & Bottle Before Filtration (g)

• Mass of Empty (Clean Bottle) (g)

• Beginning Mass of Liquid Sample (g)

• Mass of Filter Paper (g)

• Mass of Filter Paper After Drying (g)

Reference Columns:

• Solids in Liquid Sample (g)
  Formula: =Mass of Filter Paper After Drying - Mass of Filter Paper
  Instruction: Calculate the mass of solids in the liquid sample by subtracting the mass of the filter paper from the dried filter paper.

Calculation Columns:

• Solids in Liquid Sample (%)
  Formula: =(Solids in Liquid Sample (g) / Beginning Mass of Liquid Sample (g)) * 100
  Instruction: Calculate the percentage of solids in the liquid sample based on the starting liquid mass.

9. Sheet: XRF-Liquid Samples

Manual Input Columns:

• Sample ID

• Time

Reference Columns:

• Na, Mg, Al, Si, P, S, Cl, K, Ca, Ti, V, Cr, Mn, Fe, Zn, Pb
  Formula: =XLOOKUP(Sample ID, 'Spectron Results'!A:A, 'Spectron Results'!B:B)
  Instruction: Fetch the elemental composition values from the Spectron Results sheet for each element.

Calculation Columns:

• % Solids
  Formula: =SUM(Elemental Composition Columns)
  Instruction: Calculate the total percentage of solids by summing all the elemental composition columns.

• % Liquid
  Formula: =100 - % Solids
  Instruction: Calculate the percentage of liquid by subtracting the percentage of solids from 100.

10. Sheet: Weighbridge

Manual Input Columns:

• Truck Number

• Client

• Haulier

• Destination

• Product

• Order

• Date In

• Time In

• 1st Mass

• Operator In

• Status

• PCID1

• Date Out

• Time Out

• 2nd Mass

• Operator Out

• Status2

• PCID2

• Sample ID

• Sample Description from Lab

Reference Columns:

• Net Mass
  Formula: =2nd Mass - 1st Mass
  Instruction: Calculate the net mass by subtracting the first mass from the second mass.

Calculation Columns:

• None
  No additional calculations beyond Net Mass.

Data Flow and Error Handling Recommendations:

1. Manual Input Fields:

   • Ensure manual input fields map directly to the corresponding columns in the ERP system for data entry.

2. Lookup Fields:

   • Implement lookup functionality (XLOOKUP equivalent) in the ERP system to automatically retrieve data based on matching keys such as Sample ID or Sample Number.

3. Calculation Fields:

   • Embed formulas in the ERP system to perform calculations such as summing, subtraction, and percentage calculations.

   • Implement error handling (e.g., IFERROR) to prevent crashes in case of missing or incorrect data.

This completes the technical specification for all the sheets in the document. The ERP system should now be able to simulate the data entry, lookups, and calculations based on the provided structure.

Calculation Process Overview

The calculations in the document involve basic arithmetic, such as addition, subtraction, multiplication, division, and conditional logic (using formulas like IFERROR). The calculations fall into several categories:

1. Percentage Calculations: Used for calculating things like moisture content, solids in liquid, etc.

2. Subtraction and Difference: Used to calculate values such as Net Mass or Turnaround Time (TAT).

3. Summation of Values: Summing multiple values, especially elemental compositions, to calculate a total.

4. Conditional Logic: Using conditional formulas to avoid errors (e.g., IFERROR or IF).

2. Detailed Calculation Process for Each Sheet

Sheet: Sample List

• TAT (Turnaround Time)
  Formula: =IF(D3="","",IFERROR(C3-B3,""))
  Process:

  • Step 1: Check if Sample Number (D3) is empty.

  • Step 2: If it is not empty, subtract Date of Sampling (B3) from Date Analysed (C3) to get the difference (in days).

  • Step 3: Use IFERROR to handle any cases where a date is missing or invalid.

• % ATH (determined from % LOI)
  Formula: =((K3 + Q3) / 34.6) * 100
  Process:

  • Step 1: Add the values of K3 and Q3, which represent certain LOI values.

  • Step 2: Divide the result by 34.6 (a constant provided in the calculation formula).

  • Step 3: Multiply by 100 to convert the result into a percentage.

Sheet: Spectron Results

• Total Elemental Composition
  Formula: =SUM(SiO2, Al2O3, Fe2O3, etc.)
  Process:

  • Step 1: Identify all relevant elemental composition columns (e.g., SiO2, Al2O3, Fe2O3).

  • Step 2: Sum all these values to get the total elemental composition for the sample.

  • Step 3: Ensure the sum does not exceed 100%, and handle any errors (e.g., missing data).

Sheet: H2O

• Moisture @ 105°C
  Formula: =Weight of Sample And Beaker - Weight of Dried Sample and Beaker
  Process:

  • Step 1: Subtract the weight of the dried sample and beaker from the combined weight of the sample and beaker.

  • Step 2: This difference gives the moisture content at 105°C.

• % Moisture (H2O @ 105°C)
  Formula: = (Moisture / Weight of Dried Sample) * 100
  Process:

  • Step 1: Divide the moisture content by the weight of the dried sample.

  • Step 2: Multiply by 100 to convert this into a percentage.

Sheet: LOI (Loss on Ignition)

• % Loss on Ignition @ 900°C
  Formula: =((Mass of Sample (+150um) - Mass of Cup + Sample after Calcining (+150um)) / Mass of Sample (+150um)) * 100
  Process:

  • Step 1: Subtract the weight of the sample and cup after calcining from the original mass of the sample.

  • Step 2: Divide the result by the original mass of the sample.

  • Step 3: Multiply by 100 to convert this into a percentage representing material lost during heating at 900°C.

Sheet: Liquid Samples

• Solids in Liquid Sample (%)
  Formula: = (Solids in Liquid Sample / Beginning Mass of Liquid Sample) * 100
  Process:

  • Step 1: Divide the mass of solids in the liquid sample by the total mass of the liquid sample.

  • Step 2: Multiply by 100 to convert this into a percentage.

Sheet: XRF-Liquid Samples

• % Solids
  Formula: =SUM(Elemental Composition Columns)
  Process:

  • Step 1: Sum all the columns that represent elemental compositions (e.g., Na, Mg, Al, Si, etc.).

  • Step 2: The result is the total percentage of solids in the sample.

• % Liquid
  Formula: =100 - % Solids
  Process:

  • Step 1: Subtract the percentage of solids from 100.

  • Step 2: The result is the percentage of the sample that is liquid.

Sheet: Weighbridge

• Net Mass
  Formula: =2nd Mass - 1st Mass
  Process:

  • Step 1: Subtract the first recorded mass from the second recorded mass.

  • Step 2: The result is the net mass (the difference between the entry and exit weights).

3. Error Handling Process

For calculations involving dates or measurements, it's important to include error handling mechanisms to manage cases where input data is missing or invalid. This can be done using formulas like IFERROR in Excel or implementing similar logic in your ERP system.

Key Error Handling Guidelines:

• Check for Empty Values: Use conditional checks like IF or ISBLANK to ensure calculations don’t proceed with missing data.

• Error Defaulting: Return a default value (e.g., an empty string or zero) if a calculation fails or if required data is missing.

• Alerts/Notifications: In the ERP system, notify the user if any necessary input fields are left blank or contain invalid data.

4. Implementation Notes

1. Mapping Manual Input: Ensure that all manually entered fields are properly linked to their respective columns in the ERP system for correct data capture.

2. Dynamic Calculation: Calculations should be performed dynamically, meaning when values are updated or new data is entered, related calculations should automatically recalculate in real-time.

3. Inter-Sheet References: Use database queries or internal ERP functions to replicate the Excel XLOOKUP functionality, ensuring data is fetched from the correct sheet based on key values like Sample Number or Sample ID.

Conclusion

The calculations are straightforward but must be implemented with attention to detail to ensure data integrity and accurate results. Developers should follow the outlined formulas and processes closely to replicate the functionality in the ERP system. By following the steps laid out here, the development team will be able to seamlessly integrate data collection, calculation, and reference lookups into the ERP.