Building a Report App across some other Apps
Our company is trying to build a report app to show the profitability of each service route based on the customer info and some of our past data. We are a waste management company in Vancouver. We pick up clients' waste everyday and that is our major activity. In our company, there are plenty clients on each route where the clients are served, and in order to maintain efficiency or to deal with some occasional incidence, the COO often adjusts the route for some of the clients. The pic below shows the client App:
As you can see in the app, every client can be served on several routes and some of the companies are not a client yet. In the report app, we need to sum up the monthly sales field, but not all of the items. Instead, we want to sum up only for the companies that the "Client Status" is "Client!:D" and sum up by routes. Similarly, we want to sum up the total number of bins on each route, which will become a factor of our variable cost. Please keep in mind, the "Route" field in this app is a category field.
As you know, profit=revenue-cost. In our case, after getting the revenue and one variable cost factor(# of bins) for each route from the previous app, we will get other variable cost factors from another app called "Pre/Post Vehicle Inspection" where the travel information of each route is stored. The pic below is the content:
In this app, every truck driver inputs the odometer reading and route info before and after their daily service. The "Date" field records the beginning and ending time of a driver's work. "Route/Vehicle" field indicates which route it is. We want to use the report app to get the total travel distance and hours for each route in a specific period which can be selected by the user.
In the last step, the report app will calculate the estimated profit for each route by using the formula below:
Profit for route A = monthly revenue of route A - bag cost of route A (number of bins * bag unit cost) - gas cost of route A (travel distance * gas rate) - driver wage of route A ( travel hours * wage rate).
I am currently using Excel to do all of this work, but we want to make it online and easier for the users. So, if you have any suggestions or idea, please let me know. If you'd like to help us out, we are open to receive a quote at albert@growingcity.ca. Thank you very much!!!
-
Hi Albert,
I'm doing something similar, so I'll give you details on what I'm doing
You can build a report app in Podio and get it reference related app items based on a search. To do that automatically we use Globiflow and I have attached an image of a flow that makes three different searches to create three item lists that can then be manipulated for a report (sum, min, max and other calculations). The flow below triggers on 'item updated' but it can also create items based on a reporting period. Globiflow can only search for items based on text, date or calculation fields so I needed to perform calculations in the source app in order to do the the search (Open owner code is a calculation).
Once you have the right item lists related to your report app you use javascript to do just about anything - if you are good at javascript.
See my recent post called on multi-value category calculations for some example code
-
Hello Mike,
Thank you for taking a look at my case! Yes, I heard Globiflow before, I feel what you said may be a solution for us, but I've got to have a look at Globiflow first. BTW, are you familiar with Javascript? Honestly, I have forgotten everything of it since I finished the course in my university.
Please sign in to leave a comment.
Comments
2 comments