Hello all! This is kind of a general, over-a...

[Miranda_Lubarsk]

Hello all!

This is kind of a general, over-arching question about how to do something between various views/spreadsheets.

In our Main Client List, we have a Column called [Trellis Completed?] where people could mark off when they completed a trellis at each client’s house.

Then I created a view which was a map of all the trellises that had yet to be completed, so that we can see where the work needs to be done, and do jobs that are close together.

Only clients that had submitted their contracts/deposits would show up on the list, since I added a filter.

And once the client was marked as complete, they would also disappear off the list.

This year, we need to record the amount of time and materials that we spend constructing each trellis at each client’s house.

And I am trying to find ways to simplify what the crew has to input.

I would like to use the same map, etc., but I don’t want our workers to have to fill out a timesheet for completing the trellis, but then also have to check off that the trellis is complete in a different view.

My brain is just having difficulty thinking about how to do this in a single action.

I need the map to show trellises of clients who have turned in contracts) based on the Master Client List since this is where the information is recorded, but then I need it to be marked complete and disappear from the map based on a separate spreadsheet compiled by the Trellis Timesheet forms.

Your help is greatly appreciated! Thank you so much, Miranda

[Marc_Dillon]

"In our Main Client List, we have a Column called [Trellis Completed?] where people could mark off when they completed a trellis at each client’s house. "

So your users are already directly editting a client’s record by marking this column. Can you not just add time and materials column into here?

As I wrote the above, I’m realizing that the time value is probably calculated from the timesheet. So maybe you can create a timesheet where users input their times, and also the client that they were working on. Maybe with multiple fields to associate separate time amount with separate client. And on the timesheet you can have a yes/no option for if the client is finished after said amount of work. Then the main client sheet can see these referenced values, specifically the yes/no, and update their own yes/no based on it.

Where does the material used data come from?

[RezaRaoofi]

If I have understood correctly and you want your Client List’s completion to be dependent to workers Timesheet, then you will need a relationship between the 2 tables, so that in workers Timesheet you can associate the hours spent with the constructed Trellis. Once you build that relationship, then workers can fill in their Timesheet separately while you will be able to check the Timesheet hours related to each row in Client List’s table.

I believe you will need a Ref column in Timesheet table that refers to Client List, or Trellis tables.

[Miranda_Lubarsk]

@RezaRaoofi Reza, thank you.

Yes!

This is what I think will work.

However, I am struggling with the expression that will make it work.

I tried putting a virtual column onto the Master Client List that would reference the Trellis Timesheet table, but it wasn’t working in the app formula.

Basically, what I want to say with the expression is kind of like an IF or IFS, but it was saying I can’t compare a list to text or something like that.

What I want to say is this: If the column [Cuke and/or Pea Trellises Completed?] in the spreadsheet Trellis Timesheets has the value of “Yes”, AND the column [End Time] ISNOTBLANK, then show “Complete” in this column.

In the Trellis Timesheet form, the employee must enter the client’s name, which they select from a Ref drop-down menu of the client’s name, and that Ref is from the Master Client List (which is where I want to record the trellis completion.)

Does this make sense?

I’m not sure if I should be recording if the trellis has been completed in a real column, or with a virtual column.

I actually don’t think it matters to me, as long as it works on the app.

Your help would be really appreciated!

Thank you!

[Miranda_Lubarsk]

Tried this and it didn’t work, thought I could use a de-ref, but it can’t find the column.

Don’t know why it won’t work since I have the ref set up in that spreadsheet.

IF([Client Name].[Cuke and/or Pea Trellises Completed?] = “Yes”, “Complete”, “Incomplete”)