OPTIMIXER
The system is designed to enable mixing chemical materials to achieve safe mixtures for end user sales. Over the mixing process the system considers mixability, categories the mixture by safety risks, and generate all documents required for production. Assign individual prices based on material cost and production cost elements, tracks delivery time, and triggers production request.
The system intent to deliver the following metrics:
- Controlled price generation for mixed product pipeline
- Individual price calculation for Agent, and mixed product by Mixer
- User Interface for product mix creation, with automatic price and name calculation
- Automatically generate Compliance documentation – Product Safety sheets.
- Automatically generate and Send Order form for the Mixer.
- Track base material price changes for tracability
User Rights:
The system differentiates 3 main user type:
Owners: is developers user right, which provide full access to all databases
Members: is the Management Team right the team has editing rights for all database, they are able also to change views and create pages.
Agents: is the default user right to all employees with an active Microsoft subscription in the company. This right provides reading right for the site, and editing rights for lists which they will interact from the UI, but have no direct links to the DS. Agents has managed Access on selected databases, as the Order Form PDFs and Activates Products, these relations will be details in the Automations.
Service Requirements:
The system is based on following service foundations which are criteria to set up the system at the company:
- Microsoft Office 365 Enterprise Subscription for all users
- PowerAutomate and PowerApp default package
- Microsoft Teams enrolled for the organization
- Microsoft Sharepoint Service for Businesses
Data Structure
The system contains the following core tables and relations. The tables column structure is optimized for the company request
Foundational Database:
- Mixers_List: [Name, Transportation Cost (Ft/t/km), Active, Contact e-mail]
Core database which contains all the Mixing procedure authorized, and contracted service providers. Mixer Name, and key details connected to Mixers. Contact e-mail is the main channel how Mixing orders will be communicated
- Opt_ActiveIngridients: [AI_Code, AI_Description, UOM]
This list is storing all possible AI Code and Name with assigned UOM for later calculation. Any additional item, or item deletion will impact all future Opt_BaseMaterial auto generation process.
- Opt_PackageSize: [PackageSizeName, PackageSizeCode, UOM, PackageSize Value, Mixers]
List stores all possible PackageSize category assign it’s wight, and the Mixers whom produce in that Package Size.
- Opt_PriceChangeLog: [BaseMaterialName, Mixer, PriceChange]
This is log of all price changes which has been requested on any Mixer BaseMaterial combination in Opt_BaseMaterials List.
- Product_SafetyCategories: [Title, Category_Description, HTML_SAFETY_Sheet, UFI_Process, FarmConsent, Active]
The described criteria in Category_Description column is defining the Mixed Product Safety document, Safety Label signs, and the main Safety Procedures as UFI, and FarmConsent procedures. All products get reviewed by these criteria in the UI when user is mixing products.
Functional Database for Members:
- Opt_BaseMaterials: [Mixer, MaterialName, UOM, TestWeight, BasePrice, Formulation, Active, Chage Request, Safety Regulation details]
The list is storing all base fertilizer elements which could be used for mixed product creation by Mixer and formulation. The list is also storing the Actual Price of the Base Material, which could be changed by the Members and Owners. The Active Field enables Activation and deactivation of the Materials. Change Request is providing an automation which will generate interdependence List elements in Opt_BaseMaterialsAI & Opt_BaxeMaterialMixingCapacity Lists.
- Opt_BaseMaterialsAI: [BaseMaterial Name, Active Ingredient, AI UOM, AI_Volume, AmNitrat%]
The List store all Base Materials with all possible AI combination defined in Opt_Active Ingredients. Members should update the newly Updated BaseMaterial AI content. If the BaseMaterial contains N AmNitrat% needs to be provided for Safety Sheet calculation resons.
- Opt_BaseMixingCapacity: [Material1, Material2, Mixability]
This list is filled up with all possible combinations of mixing BaseMaterials. Anytime when a New BaseMaterial is created it will be paired with all other BaseMaterial in the Opt_BaseMaterial List. Members should review and Update Mixability values which will impact the UI for Users, and disable Mixing Unsafe products.
Mixability Categories: N-No, Y–Yes, Q-Quick Use, E-No Info
- Opt_ProductionCostElements: [ElementGroup, UOM, BasePrice, Currency, FinalProdcut_PackageSize, Mixer]
the List store all cost element for the contracted Mixers. All these cost elements should be considered in the calculation of the final Product Price.
Executive Lists for Agents edited through UI
- Opt_Mixes: [Opti_Mix_Name, Product_Name, Mixer, TestWeight, AI_Combination4Target, AI_Combination4OneTon, Advised_Dosage, Formulation, ProdcutSfaety_Category, VST_Number, UFI_Process, UFI_Number, ProdcutSheet, {BaseMaterial Name, BaseMaterialVolume, BaseMaterialAI, BaseMaterial_Coeff} for Each Base Material]
This list is storing all Mixed Products created by the Agents at the UI. The list is storing all details related to the Mixed Product which enables to:
- increase the reuse of already generated products
- store all safety requirements and trigger UFI process if required
- generate a standard naming convention which is align with the legal requirements
- Provide details to create Order sheet for Mixers
- Enables future connection for CRM or Order tracking systems
Automations
- BaseMaterial Update: This automation is connected to the List of Opt_BaseMaterials_List. Any item which has been created or changed triggers the automation. The automation reviews the Change Request Type and triggers switch based on the selected Value:
- New Material: The system define the combinations of the New Material and all existing Material in the Opt_BaseMaterial List – with the same Mixer. All the combinations with the mixability of E – No Info is added to Opt_BaseMixingCapacity List.
The New Material is assigned with all stored AI Code from Active Ingredients List. All combinations are generated and assigned in the Opt_BaseMaterialAIs List.
- Name Change: Triggers the Automation which is updating Opt_BaseMixingCapacity records, to apply the name change in Title Values.
- BaseMixingCapacity Updater: This automation calculates the combination of Material1 and Material2 text value as Title in the Opt_BaseMixingCapacity List. This is the lookup value on the UI, therefore name changes need to be managed correctly.
- BaseMaterialPriceChangeTracker: This Automation is triggered when a Member is changing the price of a Opt_BaseMaterials List. The Automation calculates the difference from the New price – Previous Price and add it in the list of PriceChangeLog List.
- Opt_Mixes Updater contains the following sub processes which has been triggered when a record has been added or changed in the Opt_Mixes List:
- UFI Process: If UFI Process id TRUE then the automation send the an e-mail request with the record link to the authorized responsible for the request. The approver has to update the item with the UFI number at the record. Till the UFI Number is blank or “-“ the process won’t proceed.
- Product Safety Sheet generator: The automation loads the ProductSafetySheetCategory ID and the connected HTML Document. The HTML document is updated with the Product name and Saved in SharePoint Document Library as an HTML. A hyperlink with the Document name and Link to the file location is updated in the Opt_Mixes List.
- ProductSheet Generator: The automation collects the AI Proportion Values from the Opt_Mixes List record, the VSZT number, UFI Number if applies, and Lookup the HTML_SAFETY_Sheet values from the ProductSafetyCategory List by the SafetyCategory ID. Based on all of these details a standard form of Product Sheet HTML is updated with the details above, and the HTML file is saved in a SharePoint Document Library. A hyperlink with the Document name and Link to the file location is updated in the Opt_Mixes List.
- Mixer Order Generator: This automation is triggered if an order is confirmed. The Opt_Mixes record needs to be assigned with an order volume in an Order_List which would trigger this action. The automation will generate a standard form of HTML containing the following details for the order: [Product Name, Order Volume, Account Name / details and Delivery details, Delivery Deadline, Order Date] Then Summarize all the BaseMaterials, Name, Volume for the Ordered Mixed Product, Cost of Base Materials (Ft/kg) and total Cost. Detailed description of all Cost element from the Opt_ProdcutionCostElements List considered at the calculation time.
Total Price of production for 1t of mixed product. Total Need of BaseMaterials to full-fill the order.
User Interface:
The UI is defined in PowerApp for iPad screens. Could be used from iOS Android app store through the dedicated PowerApps app, or at the web by the link provided to the users. The look of the UI is adjusted by the branding guideline of Alba Milagro Hungary Kft.
The system has a backside user authentication by the Microsoft account, which is storing account details of the Mixer creator, and could be used for tracking reasons.
NavigationDescription applies from left to right
Back button is leading the user to the previous status of the App.
Refresh button is refreshing all databases connected to the applications (look on database Structures)
Home button leads the User to the starting page
Mixer Package Size and Transportation detailsDescription applies from top to bottom
Drop down of Mixer List which filters the BaseMaterials, package size lists. Checks if the Mixer has an applicable Transportation policy and opens the transportation details below if it’s true
Transportation Panel is Requires the transportation distance and the total ordered Volume, to calculate the transportation cost on top of the price.
Agent Fee Variable
Agent fee is a variable which could be adjusted by each Agent in the App. The logo needs to be taped, and that visualize the Agent fee value in the UI. The number is set 10000 as default. This value could be changed in the UI by any User.
Product Mix Generator PanelDescription applies from left to right starting from top and going bottom.
On the TOP:
Restart button will remove all values and previous settings in the App.
OptiMix Name label is showing all products which are available in the system with a similar NPK mixture. It’s empty if the target or, the mixed product was not generated before.
Set selected product button will overwrite all values in the system with the values stored for the selected product.
Generate New Product for Agent, will create the Opt_Mixes List product assigned with the Agent who requested the product generation.
On the Bottom:
Mixability Label, which is automatically change as the Mixability value is changing in combination of any of the Base Materials selected for mixing. Label show value with the highest risk, N, Q, E, Y – check Mixability in databases
Targeted AI concentration fro the mixture. Here the Agent could define by AI what they wish to have by ha, to achive the grower soil fertilization goal.
Mixing
Base Material to be selected from the Mixers available products list
Targeted AI to be used in the calculation
Calculation request button. Pushing this button the system will check the Targeted AI Value against the Targeted AI in the selected BaseMaterial and it calculates the total Volume of Base Material needed to achieve that target.
Kg of AI by AI category. Based on the Targeted AI, the volume of BaseMaterial is defining all AI volume which will apply in the mixture.
Every New line follows the same calculation but it took in consideration of the SUM of the AI provided by previous rows.
SummaryTables: Top row provides:
The calculated Price of the Mixed materials considered all Cost elements the Variable Agent fee, and the transportation cost.
The advised Dosage of Application for the Farm to achieve the targeted AI Volumes/ha
kg of AI content of the mix/ha
Bottom Row provides:
The Unique but legally accurate name of the Mixture
% of AI content of the mix/t
Generated Documents:
