Automatically merge all demand forecast items into a single spreadsheet
Organizing demand forecasts is a crucial task that involves organizing data from multiple sources. The sheer volume of emails and the need for accuracy make this extensive, redirecting crucial time and resources away from higher-priority, strategic initiatives.
.png)
Integrate your CRM with other tools
Lorem ipsum dolor sit amet, consectetur adipiscing elit lobortis arcu enim urna adipiscing praesent velit viverra sit semper lorem eu cursus vel hendrerit elementum morbi curabitur etiam nibh justo, lorem aliquet donec sed sit mi dignissim at ante massa mattis.
- Neque sodales ut etiam sit amet nisl purus non tellus orci ac auctor
- Adipiscing elit ut aliquam purus sit amet viverra suspendisse potenti
- Mauris commodo quis imperdiet massa tincidunt nunc pulvinar
- Adipiscing elit ut aliquam purus sit amet viverra suspendisse potenti
How to connect your integrations to your CRM platform?
Vitae congue eu consequat ac felis placerat vestibulum lectus mauris ultrices cursus sit amet dictum sit amet justo donec enim diam porttitor lacus luctus accumsan tortor posuere praesent tristique magna sit amet purus gravida quis blandit turpis.
Techbit is the next-gen CRM platform designed for modern sales teams
At risus viverra adipiscing at in tellus integer feugiat nisl pretium fusce id velit ut tortor sagittis orci a scelerisque purus semper eget at lectus urna duis convallis. porta nibh venenatis cras sed felis eget neque laoreet suspendisse interdum consectetur libero id faucibus nisl donec pretium vulputate sapien nec sagittis aliquam nunc lobortis mattis aliquam faucibus purus in.
- Neque sodales ut etiam sit amet nisl purus non tellus orci ac auctor
- Adipiscing elit ut aliquam purus sit amet viverra suspendisse potenti venenatis
- Mauris commodo quis imperdiet massa at in tincidunt nunc pulvinar
- Adipiscing elit ut aliquam purus sit amet viverra suspendisse potenti consectetur
Why using the right CRM can make your team close more sales?
Nisi quis eleifend quam adipiscing vitae aliquet bibendum enim facilisis gravida neque. Velit euismod in pellentesque massa placerat volutpat lacus laoreet non curabitur gravida odio aenean sed adipiscing diam donec adipiscing tristique risus. amet est placerat.
“Nisi quis eleifend quam adipiscing vitae aliquet bibendum enim facilisis gravida neque velit euismod in pellentesque massa placerat.”
What other features would you like to see in our product?
Eget lorem dolor sed viverra ipsum nunc aliquet bibendum felis donec et odio pellentesque diam volutpat commodo sed egestas aliquam sem fringilla ut morbi tincidunt augue interdum velit euismod eu tincidunt tortor aliquam nulla facilisi aenean sed adipiscing diam donec adipiscing ut lectus arcu bibendum at varius vel pharetra nibh venenatis cras sed felis eget.
Managing demand forecasts is a crucial but tedious task that involves organizing data from multiple sources. Typically, forecasts arrive as email attachments from customers and each file often has different formats, necessitating time-consuming data cleaning and standardization. This process includes opening each email, saving attachments, converting data into a consistent format, and manually inputting it into a master spreadsheet. The sheer volume of emails and the need for accuracy make this a repetitive and error-prone task, diverting valuable time and resources away from more strategic activities.
A case study: Use of Power Automate and extrakt.AI
The extrakt.AI app can be utilized for transforming various documents into excel files and we can automate the process by getting the extractions sent directly to email. To automate the workflow, we can use RPA tools like Power Automate, ensuring that as soon as an email with an attachment is received, the CSV file is automatically saved directly to an EDI upload folder (e.g., SharePoint). You can read about the whole process here. In this blog post we go a step further and will automate the process of entering new data into an Excel table on SharePoint. We'll start by receiving the extracted data from extrakt.AI in Excel format via email. Then, we'll use Power Automate to transfer that data from the email attachment to our master Excel file on SharePoint. You can see the whole process in the video below, but we have also provided a step-by-step guide here, so you don’t have to do them manually, but can simply import them to Power Automate.
RPA - Power Automate Setup
- Create a New Flow
a) First, go to Power Automate and, on the left, you will find: + Create button.
b) Select Automated cloud flow and name it: “Copy data to a Master file” (or whatever you want). Flows trigger is SharePoint; “When a file is created or modified (properties only)”.
- When a file is created or modified (properties only)
a) On your SharePoint choose a site address where your excel extraction files are being saved to (learn here how to automate this step).
b) Choose the Library where your excel extraction files are being saved to.
- Create table
In order to transfer data from one excel document to a table in our master file, we have to make sure the extracted data is in a table as well. Here’s how we can do that:
a) We add an action from Excel Online (Business): “Create table”.
b) Under “Location” choose the SharePoint Site where all your excel extractions are saved to.
c) Under “Document Library” choose the correct Document Library on SharePoint where all your excel extractions are saved to.
d) For a “File”, do not click on the folder icon. Here we need dynamic content, so first click in the text bar, and then on the blue lightning (dynamic content), that will appear to the left or right of the text bar. Search for SharePoint: “Identifier” and select it.
e) “Table Range” is a bit tricky, since we don’t know how many rows our document will have, and each Demand Forecast sent from customers will most likely have different amounts of rows. For this reason, we want to define an excess number of rows in Table Range than what we expect our documents will have. These rows will blank once the table is made, but not to worry, we will eliminate them in a later step. In this case, I know I will have 4 columns and no more than 50 rows, so this is what I will write as the Table range: A1:D50.
f) From advanced parameters choose: “Columns names” and write the names of your columns exactly as they are, separated by comma e.g.: Material ID, Customer ID, Date, Quantity.
- Delay
Add a "Delay" action. We are doing this to make sure Power Automate will have enough time to create a table within excel file that just arrived on SharePoint, before moving on to the new action. 1 min is enough time for my file, but make sure you also check and customize this time based on your own amount of data. At the end, when we’ll test our flow, if you’ll get a failure at the “Create table” step, you have to increase the delay.
- List rows present in a table
So far, we have received our extracted data in the excel file to SharePoint, put that data in the table and now we have to define the data in our table. The first three steps (a-d) are the same as in step: “3. Create table”.
a) We add an action from Excel Online (Business): “List rows in present table”.
b) Under Location* choose the SharePoint Site where all your excel extractions are saved to.
c) Under Document Library* choose the correct Document Library on SharePoint where all your excel extractions are saved to.
d) For a File*, do not click on the folder icon. Here we need dynamic content, so first click in the text bar, and then on the blue lightning (dynamic content), that will appear to the left or right of the text bar. Search for SharePoint: “Identifier” and select it.
e) For Table* we need dynamic content as well, so first click in the text bar, and then on the blue lightning (dynamic content), that will appear to the left or right of the text bar. Search for Excel: “body/name” and select it.
f) From advanced parameters choose: Filter Query. This is where we filter out the empty rows that we don’t want and have created in step 3. - e) Table range. Here choose any column name and add “ne” for not equals to, then either the word Null or two single quotes. You don’t have to choose all the column names, just one. I choose Quantity, so here is the example on how to write it:
1.) Quantity ne ‘’
or
2.) Quantity ne Null
This way no empty rows will be uploaded to your master file.
- Apply to each
a) We want to make sure that data from every new attachment is copied to the master file, so what we need is a control called: “Apply to each”.
b) Click first in the text bar and then on the lightning that will appear to the right of the text bar and search for: “body/value”.
- Add a row into a table
a) Inside the "Apply to each" loop, add a new action Excel Online (Business): “Add a row into a table”.
b) Under Location* choose the SharePoint Site where all your excel extractions are saved to.
c) Under Document Library* choose the correct Document Library on SharePoint where all your excel extractions are saved to.
d) For a File*, click on the folder icon and choose the master file, where you would like your data to be copied to.
e) For Table* we need to select to which table you would like your data to be copied to. In my case this is Table1.
f) From advanced parameters select all your columns. For each column we will have to write this formula/expression: items('Apply_to_each')?['ColumnName'].
This is what it should look like:
- Material ID:
items('Apply_to_each')?['Material ID']
- Customer ID:
items('Apply_to_each')?['Customer ID']
- Date:
items('Apply_to_each')?['Date']
- Quantity:
items('Apply_to_each')?['Quantity']
- Save your automation
In the top right corner click save. Now every attachment coming from the noreply@extrakt.AI email will be sent directly to your chosen folder on SharePoint.
Next, let’s create an integration to convert these different types of documents into an EDI-friendly format, in our case a CSV file, which will be sent directly to your email.
Extrakt.AI Setup:
- Create a Template
a) Go to the extrakt.AI app in the sidebar look for: Create a Template.
b) Scroll down to the sales tab where you will find Demand Forecast Report Template. Add or remove the fields to customize it and once you're done, click Create in the top right corner.
- Create an integration
a) Go to the sidebar again and find Integrations. On the top left find a Plus sign and after that click Email icon.
b) Scroll down to find your template and check the box in front of it.
c) Scroll up and go under the Result Email tab, keep Reply to sender box checked.
d) Where you see Email attachment format, choose your preferred format (in our case CSV).
e) I don’t want my email to include the original attachment as well, so I will uncheck Include input with the email box.
f) Once you are done, go to the top right corner and Create Integration.
- Forward email
a) You can find your new Integration under Integrations, where you can copy the unique email address.
b) Forward suppliers email, with the document attached to this unique email address pertaining to the Demand Forecast template.
- Result
.png)
.png)
.png)
.png)