Hello all, So I am trying to do a reference ...

[Miranda_Lubarsk]

Hello all,

So I am trying to do a reference or a de-ref but it is not working for me.

I have a main spreadsheet called “Master Client List”.

I also have a Trellis Timesheet spreadsheet, and one of the columns in this is a Ref drop-down menu of the [Client Name] column from the Master Client List.

This works.

As we input new rows/forms into the Trellis Timesheet for each client, I want it to be recorded in the Master Client List, so that we know the trellises for that client have been completed.

So I created a column in the Trellis Timesheet called [Trellis Completion].

This will read as “Complete” when a form is input for any given client.

Then I have another column in the Master Client List called “Trellis Complete”.

I would like this to show the same value as the [Trellis Completion] column in the Trellis Timesheets spreadsheet, for the referenced client.

So if we complete Betty Smith’s trellis, I want it to show as “Complete” in the Master Client List under Betty Smith, even though all we have to do is enter information into the Trellis Timesheet.

I have made sure that the ref for [Client Name] is there (it’s a drop-down list in the form), and the Client Name is both the key and the label in the Master Client List AND in the Trellis Timesheet.

However, when I try to do a de-ref, for example [Client Name].[Trellis Completion] it does not work.

It says it is invalid.

I am putting this in the app formula field.

And I have tried making the type both a Ref as well as Text.

Neither have worked.

What am I missing? Your help is greatly appreciated. Many thanks, Miranda

[RezaRaoofi]

The De-reference in your column structure only works if you want to grab a column value from Client List and use it in Timesheet, because the Ref column is defined in Timesheet table.

But if you want to get that complete conclusion the other way around (from Timesheet to Client list), then it would mean the primary decision of completion is stored in Timesheet table, am I right? If yes, then can you explain the business logic? Because I assume the Timesheet table could have several rows each having a a [Complete?] column; how does that work then?

[Miranda_Lubarsk]

Reza, I’m not exactly sure what you are asking me.

What do you mean by “primary decision of completion”?

And what do you mean by “complete conclusion”?

This is terminology that is awkward and I don’t understand what you are asking.

I need to know if the trellis has been completed in the Master Client List, because I am creating a Trellis map (consisting of trellises left to be completed) based on the address information on the Master Client List, as well as other factors such as if the client’s contract is in.

This is all recorded on the Master Client List.

The Trellis Timesheet table should have one entry per client, and yes, each client would be a new row.

I still don’t know what you are asking me.

Help!

[Miranda_Lubarsk]

This is the column structure for the Trellis Timesheets:

[RezaRaoofi]

Do you have a column named [Client Name] in both Master Clients List and Trellis Timesheet tables? If yes, then the formula that you put in a Virtual Column in Master Client List table should be like this: LOOKUP([Client Name], “Trellis Timesheet”, “Client Name”, “Complete”).

[Miranda_Lubarsk]

Ah!

Thank you, I think I just forgot the brackets and quotation marks.

It finally worked! Many many thanks @RezaRaoofi !

[RezaRaoofi]

@Miranda_Lubarsky you are welcome!

[Miranda_Lubarsk]

@RezaRaoofi I want to make sure you saw my last post, but I forgot to put your name

[RezaRaoofi]

I meant which is the first table that stores the completion status.

Reading your next comment, I think that is Timesheet table where the completion is decided and stored the first time. Since Timesheet table only has one row per client, you should be able to just use LOOKUP() function in ClientList table in a Virtual Column and get the value of [Trellis Completion] from Timesheet table.

[Miranda_Lubarsk]

@RezaRaoofi Ok, so I tried this in a virtual column: =LOOKUP(_THISROW, Trellis Timesheets, Client Name, Trellis Completion)

But when I filled out a trellis timesheet just to test it out, the information does not appear in the Master Client List where the LOOKUP expression is.

Thoughts?

[RezaRaoofi]

The first parameter of LOOKUP() must be the column or expression you are looking for; replace _THISROW with that column. Perhaps [ClientName], if that is a valid column name.

[Miranda_Lubarsk]

I did that, and I’m still not seeing it pop up on the Master Client List.

Any ideas?

[RezaRaoofi]

Can you provide more details and screenshots of the 2 table’s column structure, and where you have entered the current LOOKUP() formula? You can always press test button in expression editor and test that formula too.

[Miranda_Lubarsk]

@RezaRaoofi I tested the formula and it verified.

I also verified & saved a couple times.

Here are pictures of the Master Client List column structure (only including the bottom because it is pretty big).