Filtering and using related records data on a submission form based on loged in user criteria

Community Discussions How Do I
#1

Hi

New to this forum and to Tada (only been playing with Tadabase for 2 days)

So here is my little issue, basically, how do I use a related table records in the form I am working on

Let me illustrate

I have a basic user table: USERS(name, email, role, location join)

Each user (record) on the Users table is connected to a single location (that they belong to) in the table: LOCATIONS(location, region)

We also have different location machinery where each machine belongs to a single location and a location can have as many machines as we want. Following table illustrates this: MACHINES(location join, machine name, hourly throughput constant)

We then have a shift report that needs to be filled in by the user. The user logs in into the system clicks on the report and fills in the shift report that ends up in the table: REPORTS(location join, machine join, hours worked, hourly throughput constant) . Each Report will be connected to the Location (via user), Machine (selected by user and based on Machines available at the location).

The Report is a Form Page that really only collects two pieces of information, what Machine is used and the “hours worked” collects the data. The Report is the key tool for data gathering and the main focus for the real users.

image

Here is where I struggle to get this to work

A) Once the user logs in and opens the Report Form the Location is automaticly defined via the User’s “Location” and is later filled on the Report via record rules. At the same time, as the location is known (user is logged in and has a Location) I want the form to automatically filter all Machines belonging to the Location of the User so that the user can select a Machine from the Machines available in the location. I cannot get this to work

B) In the next step, once the machine has been selected, I want to be able to use the specific Machine’s “hourly throughput constant” in the Report or ideally use it to calculate the throughput by multiplying “hours worked” with “hourly throughput constant”.

I have tied to get this to work and got stuck at point A above. I have done this in Caspio via cascading dropdown fields but I just don’t seem to be able to do this here. Any help highly appreciated.

#2

Hello @trixaren, welcome to Tadabase and our community!

While record rules set the “Location” to the Logged In User’s Field of “Location,” you’ll still need to require the user to select the Location manually on the form for a dependent dropdown to work. In your case, that would be Machines connected to this form’s Location.

You can, however, limit the Location to the Logged-in User’s Location using this filter and then show Machines that are connected to this form’s Location using a dependent dropdown filter.

Another option you may want to check out is adding related records while filter by connected records. This would mean you would have the user navigate to a Location details page. On the Location details page, you would add a Report Form that inserts Reports connected to this Location. You may then add the Machine connection field and filter using the “Show” option “Machines connected to this page’s Location.” You may find more information on this filter by clicking here.

For Step B, can you please elaborate a bit on what an hourly throughput constant is? If you’re looking to calculate two numbers using a formula, I recommend checking out the Basic Formula field.

#3

Hi @Chem , thanks for the help!
I got to the same result as you describe (Tim was very helpful). I was hoping that there was a way to auto populate the location field at page load or to auto filer based on logged in user fields. The latter would be a useful functionality.
As to the second part, that one is solved - much simpler than I thought.

1 Like