How do I connect team members to "jobs" and v...

AppSheet AppSheet Q&A
1

How do I connect team members to “jobs” and vice versa?

Inside the “events details” is listed who’s playing the event: Drums John Doe

Bass Jane Doe

How do I format it so the name inside the Event Details is a link to the person’s “contact info”?

Also, how would I show the events that a person is booked on inside of their contact detail? Example: I click on my menu “Band and Tech” and its a roster of all my team. I click on John Doe and all his contact info comes up but also a link to the Events they’re booked on?

2

Create a virtual column with all formula select(Events details[Events Name],[Name]=[_thisRow].[Name]).

https://help.appsheet.com/expressions/expression-types/list-expressions-and-aggregates

3

For the 1st question… if you could use a ref field from the contacts table, it would have the link automatically.

4

@Aleksi_Alkio What field should I create as a REF field? There’s no fields in EVENTS and BAND TECH that always match. My BAND TECH table is just an address book that hold all my employees’ info. Doesn’t a REF field have to be a field that has the exact same info in each table/sheet?

5

This case is solved. The answer was a ref field for the employee column in event’s table.

6

Thank you for the replies! Unfortunately, I’m a little out of my league here. I’m a noob and only understand basic stuff.

If anyone would be interesting in working on this with me I’d be glad to talk and compensate time.

7

I’m watching some APPSHEET videos and have a small understanding of references.

My Sheets are setup as such

Sheet Name: PHONE APP - which has all the event info (event name, venue, address, employees booked, misc details, etc)

TEAM - which has my roster of employees.

I’m trying to connect these two so if i click on my ROSTER (named “Band and Tech” in my app), and I click on an employee, I can see which EVENTS they’re booked on.

Alternatively, if I’m in an EVENT, I can see the employees booked for the event and I’d like to be able to click on their name and see their ROSTER card (all their contact info from the TEAM sheets).

I don’t really see any logical REF field between two sheets unless I’m missing the concept of REF fields?

Can someone help?

8

Howdy Aleksi! I need something very similar to this and need a little more guidance.
I have a Members Table and Members View, but I in this View, I only want to see basic Member data, Name, Address, Mobile Phone, and then just a Ref Link to their entire Row in Members Table/all of their Contact data.
I made a Virtual Column and entered this formula in the App Formula: select(Members[Name],[Name]=[_thisRow].[Name])
but get this error: “The expression is valid but its result type ‘List’ is not one of the expected types: Ref”
It seems a Virtual Column is best here, because I really don’t need that [Details] column in my Spreadsheet. Thank you!

9

Wrap your SELECT() with ANY():

ANY(select(Members[Name],[Name]=[_thisRow].[Name]))

Note, too, that if the Name column of the Members table is not of type Ref, you’ll need to change the type of your virtual column.

1 Like
10

OK, cool, makes sense. Forgot about the “Any”…Doh! Will report back, thanks Brosef!

1 Like
11

OK Steve, my [Name] in my Members Table is NOT a Ref, so I changed my VC to Text and used that ANY(select(Members[Name],[Name]=[_thisRow].[Name])) formula. It works just fine, except in [Details] I now only see the Name of my member, which when clicked does indeed take me to that Member’s Details, but it doesn’t have that intuitive little arrow that you usually see for a Ref to tell Users that this will take them somewhere else, to all contact data for that Member. I need to see that > arrow. I know this must be easy and I’m a doofus…haha. Thanks again!

12

So, now I’m thinking I’m an idiot, because I guess I really don’t need a Details Link/button here because I can just limit the data that is shown in my Members View, and if User wants to see all Details, they simply click on that persons Name. My Client has asked for a “Button/Link” to Member contact details because that’s what he is used to in his legacy (=old, archaic, 1995 style ridiculousness) software. This might be a case of whatever you Pros call it when the new Dev gets caught up trying to deliver exactly what Client asks for, when, in reality, the Devs new platform/technology can do required function much better, but a little differently. I call it Shoehorning, when New Tech is ignorantly/arbitrarily forced into the Old Tech form because…stupid.