Can you turn on interactive mode in dashboard, for two different tables but have the same unique ID

Tuskan
Participant II

I have two tables, “User Database” & “Job 001 Applications”, both have varying data points, but an applicants unique ID will appear on both tabs, to link user data to a users application and that specific data.

In theory, user data remains the same, however their application data may very for each application, ie Job 001 & Job 002.

I need to display both tables data in a dash board, in 3 views, user data, user detailed view, and the application specific data.

When I activate the “Interactive Mode” for the dash board, obviously the User Data and detailed user data, are correlated and so both change in unison, but the application data does not change, to newly selected user, thus not showing the correct application data for the user that is selected (it has to be done manually). Any solutions around this?

Any other ideas, are always welcome. Application

Application data kept separate from user database, but is linked to a user.

Solved Solved
0 11 1,112
  • UX
1 ACCEPTED SOLUTION

Hi @Tuskan,

Thank you for more details. These certainly help. If I have understood your requirement correctly, you could create the dashboards in any of the following two approaches.

  1. 3 Slice , 3 Dashboards approach: Please create a slice based on filter expression something like

IN([User ID], SELECT(Applicants[User ID], [Status]=“Offer Made” ) ),
Please have 2 more similar slices for other two statuses and similarly 3 dashboards with the first with based on each of the 3 slices and next two views based on detail views - identical for all three dashboards.

  1. One dashboard with another filter input table.
    A) You may have a 2 column 1 row filter table called say “OfferStatus” table with an enum column called say [Offer Status] with options such as , “Offer Made”, “No offer” etc.

In the slice filter of the Database table expression you could have an expression something like

IN([User ID], SELECT(Applicants[User ID], [Status]=ANY(OfferStatus[Offer Status]) ) ),

Please include the three views as earlier and a fourth detail view based on OfferStatus table that allows the user to select the types of offers through enum button.

The OfferStatus table can have only one row, if only one user uses the app or you will need to include more rows in the OfferStatus table, one per each user, to retain selection of each user if many users will the app simultaneously.

View solution in original post

11 REPLIES 11

Is the “Job 001 Applications” table child table of “User Database” table?

Edit: Also if so, out of multiple applications that a user may have which one you would like to display -latest one?

Hi @Suvrutt_Gurjar, the tables are independent of one another, but if an application record exists in “Job 001 Applications”, it will have an unique id linked to a user in the “User Databse”, both tables will have the same key, to identify a row.

To answer your second question, ideally only one entry will exist per user, as “Job 002 Applications” will be a new tab, for a different job and its applications.

Thank you for the update. You may keep the following approach handy just in case it helps.

The approrch described below needs two tables in parent child relationship. I believe having such parent child relationship for related data between tables in general helps in easier access and navigation of information within the app.

If the two tables are in parent ,child relationship, then you can have system detail detail views of both the tables next to deck view of parent table , that is Users table in you case. You can include these three views in the dashboard.

Then if you tap on the deck view on any user record , the corresponding detail record of the
user and the latest job application detail record can be displayed. A sample of a similar set up below.

In the example below, Order ID in the leftmost view is equivalent to Users ID( or Name ) in your case. On selecting an Order Id, the detail view of that order ID and all related line items or order details ( applications for that user in your case) for that order is displayed in the next or middle view. All Order ID s are highlighted in red for easy reference. You will see Order ID ( User ID) is same . In the third view at the extreme right, detail view of the topmost related line item or order detail ( application in your case) is displayed, highlighted in green.

On tapping other order details in second view , the order detail view of those order details are displayed but as a full screen and not in dashboard mode.

Tuskan
Participant II

Thanks for this above @Suvrutt_Gurjar , that could be a life saver solution, currently looking how to create parent and child relationships on, any resources?

Sure. AppSheet has very detailed help documentation.

Please go through following articles. Please do evaluate and satisfy yourself that referencing ( creating parent child table relationships) is the best approach in your case overall and not for just solving this above requirement.

Below sample app demonstrates use of referencing well. In fact, I also used it in my post in the response above.

https://www.appsheet.com/samples/An-app-for-managing-customers-products-and-orders?appGuidString=245...

Edit: Minor change to description

Thanks for this, huge help, much appreciated!

Tuskan
Participant II

@Suvrutt_Gurjar, so the image/example taken above, uses a the slice with the formula IN([User ID],Applicants[User ID]), as a slice to take data from the main database, and filter it based on Applicants who have applied to this particular job.

I was able to successfully get the above working, but now need to create 3 dashboards. Based on “Status” type , a column in the applications table.
[Status] either Offer Made, No Offer, Applied etc.

What would be the best way to build these, through a unique slice for each, or using the original slice, but filtering on a UX/display level. Trying to find a formula to do so, Such as Filter(Applicants, [Status]=“Offer Made”), but keep getting a

“The expression is valid but its result type ‘List’ is not one of the expected types: Yes/No”

Perhaps you can filter or sort using the IN function on a slice level across two tables?
IN([User ID],Applicants[User ID]), but add a constraint, such that [Status]=“Offer Made”, within the applicants tab, not sure if this is possible?

Three tabs, filtered or sorted by 3 different constraints (Offer Made,Applied, Shortlisted) for example

Hi @Tuskan,

I am sorry that I am unable to understand the latest configuration you have shared.
Have you applied parent child relationship?

Also could you please update on what tables the first two views shown above are? The third ( extreme right) view seems to be on the Applicants table? Also you have used two nomenclatures “Applications” and “Applicants” tables? Are these different tables or one and the same?

Edit: Corrected some spelling mistakes.

Hi @Suvrutt_Gurjar

Apologies let me try explain better, the last picture may have been confusing.

I have ref’d, the user ID in the “applicants” table to the user ID in the Database. Thus creating the relationship. Which worked to link the 3 views, so that they change in unison based on the user that is selected. However this current view includes all applicants, regardless of their [Status].

Idea is to, create 3 dashboards, “New Applicants”, “Shortlisted”, “Offers”, that only show the related rows associated to a user in the Database, if their application in the applicants tab has a particular [Status] .

So if the user clicked on “offers”, all the corresponding candidates, who have their status in the applicants table set to “Offer Made”, are displayed.

looking for a formula to apply this to either a 1.) slice, or under the 2.) Display options of a view, Show if: X is true

not sure of the formula and or best approach between 1.) or 2.)

Hi @Tuskan,

Thank you for more details. These certainly help. If I have understood your requirement correctly, you could create the dashboards in any of the following two approaches.

  1. 3 Slice , 3 Dashboards approach: Please create a slice based on filter expression something like

IN([User ID], SELECT(Applicants[User ID], [Status]=“Offer Made” ) ),
Please have 2 more similar slices for other two statuses and similarly 3 dashboards with the first with based on each of the 3 slices and next two views based on detail views - identical for all three dashboards.

  1. One dashboard with another filter input table.
    A) You may have a 2 column 1 row filter table called say “OfferStatus” table with an enum column called say [Offer Status] with options such as , “Offer Made”, “No offer” etc.

In the slice filter of the Database table expression you could have an expression something like

IN([User ID], SELECT(Applicants[User ID], [Status]=ANY(OfferStatus[Offer Status]) ) ),

Please include the three views as earlier and a fourth detail view based on OfferStatus table that allows the user to select the types of offers through enum button.

The OfferStatus table can have only one row, if only one user uses the app or you will need to include more rows in the OfferStatus table, one per each user, to retain selection of each user if many users will the app simultaneously.

@Suvrutt_Gurjar Thank you! I went with option 1, and it is working fantastically!

Top Labels in this Space