Sometimes you can’t, or don’t want to, add another program or website to log on to and use in your day to day business. This was the case with one of our recent customers, which lead to a different and exciting implementation of Bosbec WE.
The customer wanted to register their sales team members in recipient groups so that their team leaders could easily manage the lists and so that campaign messages could be sent to all current sales members. This would usually be a pretty straight forward task in the Bosbec admin panel, but they did not want to ask the team leaders to log on to a new website to manage this.
A suggestion was that we send the list of current team members in a CSV-file to the team leaders via email. They would then be able to make changes to the file and send it back to a Bosbec email address for a workflow to pick it up and make changes accordingly. However, this would mean that there was a risk that files were mixed up, an older file could be used, two files with different changes could be submitted at the same time by different team leaders, and some data may be lost as a result.
As the customer was used to working in Excel, they asked if we could automatically update a single shared Excel file instead of sending new files daily. We found that this would indeed be possible by uploading a file to the Bosbec File Service and using Excel’s “Power Query Editor.”
A workflow was set up to send a text message to all team members asking for approval of the collection of their information. A form where they were asked to enter their details was also provided in this message.
For each submitted answer, the workflow would be triggered and a unit created with the provided identification and contact information. This unit was then added to the recipient group for the respective team.
When this process starts, a CSV file is created with all group members’ information and uploaded to the customer’s FTP account. When this was done the first time, Bosbec File Service created a direct link to the file we could then use in the Excel file. Future responses modify the group and the file, but do not change the link. This allows us to use it in our Power Query in Excel.
An Excel file was created with three different sheets: “Team members,” “Control,” and “Settings.” The direct link to the CSV-file was added to the “Settings” sheet. This cell was then defined as the source for the table created by the Power Query. This is the table the team leaders interact with on the “Team members” sheet.
As the source is based on the current list of group members on the Bosbec platform, the team leaders will always have an updated list of team members available as long as the Power Query is executed when the file is opened.
We still had to come up with a way for the customer to remove members from the list without logging into the platform. The list is updated according to the groups every time the Power Query is run, and any changes made to the file through Excel would be reverted.
We decided to create a form through which the team leaders could submit the team member’s employment ID. This form was added to the “control” sheet and styled as a button. When this form is submitted with a valid ID, a workflow searches for a unit matching this ID and removes it from the group. The source file on the FTP is updated with the new group data, and the list will be up-to-date.
Different businesses have different wishes, needs, and requirements, and the services and systems used in the company must be designed or modified to fit how the business operates. This solution is an excellent example of how Bosbec WE can support and adapt to the day to day business with minimal changes to your company’s operations.