Can I use a reference in this situation?

[SKETCHwade]

Hi everyone,

I am creating an app that allows me to certify people for work.
I have a view that displays all of the users, along with all of the jobs they are certified or not certified to complete.
I have another form view that allows me to ask them questions and input their responses.

I would like to link these up. So if a row has been input on job 1 in which user x has passed it will show up as passed on the main view.
In the other direction I want to be able to go to a user on the main display and use an inline action to send me straight to the job form with the name prefilled.

I imagine I can do this using references but I am unfamiliar with exactly how these work.

[WillowMobileSys]

SKETCHwade:

So if a row has been input on job 1 in which user x has passed it

This needs a little clarification. Some images of your views might help.

What does “input on job 1” mean?

Can you elaborate on what “user x has passed it” means? How does the user pass? Where is this pass/fail decided…in the form? Is the form data in a different table?

[SKETCHwade]

Sorry @WillowMobileSystems

I have a list of users as you can see in the first image.

Each user has a yes or no for each job they are certified able to complete.
If I complete the certification form for that user I want it to be marked as certified on the user view.
I believe I have this largely working now. In the pictures you can see the reference listed below the users details.

Is it possible to remove this and just have a link to that users passed form next to the related job.
For instance I have a job labelled Artic, user Bob for example has just completed his certification. I would now like to see on Bobs detail view has Artic marked as completed and by clicking on that “Completed” text I am shown his completed form.
This sounds very complex but I am sure it’s basic level appsheet work.

[Steve]

These warning indicators need to be fixed (assuming it’s not something you yourself have added):

The yellow triangle typically means the column is of type Ref but the value does not match a key column value in the referenced table.

[WillowMobileSys]

Ok got it! With the images it is much more clear and it is fairly basic as you mention. Was just hard to visualize from a text description.

First things first, like @Steve mentions, you need to correct the Ref column that is pulling in the Employee names. You do this by making sure the VALUE stored for each row is the KEY column in the table you are referencing. You can still show the Names by setting the Name column as the LABEL for that table. AppSheet will automatically translate the KEY value into the LABEL value for display.

Secondly, you’ll need to decide how you will be reflecting the Pass/Fail results in the Users view. As you have already noticed, Yes/No columns do not show the column headings. I would recommend setting each column for the different Certifications as Text and then assign values like “Pass” or “Fail” or blank if not yet completed.

Once you have the two things above settled, then you can link to your Form Certification results. I’ll assume you are saving these results to a different table named “Certifications”, you have an “Employee” column and you have a result column named “Pass?” defined as a Yes/No column with “Yes” for Pass and “No” for Fail. If so, then you can set the App Formula for each column like this:

SWITCH( 
       LOOKUP([_THISROW].[Employee], "Certifications", "Employee", "Pass?"),
       "Yes", "Pass",
       "No", "Fail",.
       ""
)

I hope I am on the right track here and this helps. @Steve please chime in if I have mis-represented anything!

[SKETCHwade]

@WillowMobileSystems

Thank you for your very detailed response!

So the structure of my app is a little bit different to your interpretation but that’s fine.
I’ve got it mostly working (Not giving errors haha), the issue im running into right now is with the expression:

SWITCH(
…One randomly chosen value from this list (
…The list of values of column ‘Passed?’
…from rows of table ‘Artic’
…where this condition is true: ((The value of column ‘Operators’) is equal to (The value of ‘Operator’ from the row referenced by ‘Operator’)))
…“Yes”
…“Pass”
…“No”
…“Fail”
…"")

The result of this is that the column shows the text in that cell (As though theres no formula for the column and its a simple text column). I feel like the result means it is looking up the wrong table but it cant be because its not producing errors and the Operators table does not have a [passed?] column

[SKETCHwade]

Hmm, It works perfectly… If I put the formula in a virtual column… why is this?

[WillowMobileSys]

What is the formula you ended up with? What issue do you have if you try to use it in a normal column?

Honestly, if you do not need to save the Pass/Fail value in the “main” view for any reason, then Virtual Columns might be the better choice anyway.