I wish to develop an interactive web application which provides the estimated staff requirement under different scenarios. The staff is divided into two categories, fixed and variable. The fixed staff is base staff and will apply to all scenarios while the variable staff requirement depends upon different factors, which the user will have to input. The application should display the total staff i.e., fixed and variable staff, for the given set of variables. The details of staff requirement can be provided in a database, from which the application will generate the output. Where do I start, and how do I proceed? Any tutorials, templates or relevant resources would be of great help.
Hi Samdani, welcome! If I understand you correctly, you’d like to have users submit a survey of their requirements, and have your Softr app produce a calculation based on their responses? If so, I’ve actually built something that works very similarly.
Essentially, all of the calculations are pre-assigned values that you can feed into your data source. Here’s what your data source could look like:
Calculations table: where custom calculations are done
Features table: the multiple-choice options you’d like to present your users
The user journey would look like this:
User submits a form
Form submission creates a record in the Calculations table
Upon form submission, the user is redirected to a Calculations details page that dynamically loads their most recent submission
The general idea is that you’ll link the tables and weight the features (e.g., if a user selects Feature 1 which requires 2 staff and Feature 2 which requires 1 staff, the resulting Calculation record will produce 3 variable staff or something similar) in your back-end (e.g., Airtable), then show it to the user in Softr.
If you share your database configuration, I’d be happy to elaborate.
Thank you very much Michael for such a detailed response. The logic is slightly different from what you had mentioned.
I’ll have one table which lists the staff details of the base staff, for example
Staff A - 02
Staff B - 01
Staff C - 05 and so on
I may have to have a second (or more) table(s) for the details of the variable staff, for example
Staff P - depends on variable x : x1-01, x2-02, x3-03
Staff Q - depends on variable y: y1-06, y2-08
Staff R - depends on variable z: z1-03, z2-06, z3-09, z4-12
(all the levels and the staff numbers under different scenarios are predetermined, meaning they do not change)
The user submits the levels of variables x, y and z (from the dropdown menu) in the form (for example, x2, y1 and z4). Then, the total staff will displayed. For example,
Staff A - 02
Staff B - 01
Staff C - 05
Staff P - 02
Staff Q - 06
Staff R - 12
Total - 28
The table for the base staff may be straight forward, however, I’d be needing help for setting up the tables of variable staff, and configuring the database. Which database tool should I be exploring to test this use-case (I prefer a simple tool, in terms of learning curve, as this is my first-ever potential app)? Would also like to know if I can use any of the (or part of the) readily available templates for the front end (UI). Thanks in advance.
You should be able to accomplish this with a lightweight database such as Airtable - it will require you to create Formula fields.
How I would go about this is setting up 3 tables, one for Staff, one for Variables (which I previously referred to as ‘Features’), and one for Calculations.
In the Variables table, list out all of the different variables and assign each record a score for level. Then, create a relation to the Staff table and link each Staff record associated with a Variable. For example, if variable z affects Staff A, Q, and R, then I would add each of those records as a relation to the z record in the Variables table.
You’ll then use your third table, for example, Calculations, to determine Staff and level for each form submission. You’ll use the formula fields in Airtable to create a long formula with nested if functions to calculate the score for each variable and output the desired Staff.
Essentially, you want a way to tally levels for each variable separately, and then apply conditional logic to automatically determine which Staff are relevant.
In terms of UI, I don’t have any context for what you’d like to achieve, but you could start with a simple page with a form and a Calculations details page to display results.
Since different staff positions (A, B, P, Q) have different formulae for calculating the staff requirements under various scenarios, there must be a provision in airtable to enter different formulae for different rows. But I’m afraid that’s not possible in airtable. Is there a workaround?
You would need a series of IF() statements. The other workarounds are just abstracted version of this (e.g., a separate table for each position with one formula each, or one formula column in the original table for each position).
If you’re stuck, feel free to send me a DM and I can try taking a stab at it in my free time.