Help With Ref's

[Luke_Vancleave]

Hello, I have a project management app where there is an inventory of items used at a list of client’s worksites by a list of personnel. I have everything working pretty well at this point except for a couple of key features.

I have my inventory levels affected by creating movement or action records. Basically separate worksheets in the spreadsheet that grab Item ID #'s and tell them to go to or from location ID #'s. The spreadsheet then reads that and based on where it is or going to changes quantities at locations.

This all works fairly well but I’m trying to tie another element into this where I also have a Client ID # associated with worksite locations. So I’m trying to have specific movement and action records referencing a client if they were done at a location with a client associate with it.

For example,
Here is my sheet of Client info with their ID #'s.

These ID #'s get associated with a location in the locations list Sheet

Next, in my movement record form, I have the user fill out where they are moving an item from which references a list of “Active” inventory locations. Then they choose an item which references a list of Items at that location. Then they choose a similar list of active inventory locations for where they are moving it to and then a quantity they want to move. This form also date stamps and user stamps this.

We can see this in the sheet where this form enters data. Based on these ID #'s, other sheets look at this and calculate where quantities of items should be. You can see at the right side there are two more columns referencing what client (If there was one) was associated with that location moving FROM or TO. This is so that I can reference later by client what got moved or an action taken for an item at for the client.

Here’s where I’m running into a problem. We can see that when we open the data column viewer the FROM, ITEM, and TO fields are all easily made into reference types. However, because I need to keep this form simple, I need the CLIENT ID FROM/TO columns to be automatic so that when the user simply enters the locations FROM and TO it automatically references what clients are associated too. However, right now I can only do this as a TEXT column type and not a REF column type. When I try to put this formula into the Auto Complete section of the column, it tells me my expression isn’t a REF but a LIST and therefore can’t compute.

For the time being this is the expression I have now that will at least spit out the correct number in the column, but it doesn’t create that reference link that I can use in the app.

So in short, I need a way to make these columns on the tail end of my movement form automatically reference the client that is associate with the location they are being moved to or from. Any ideas? Thanks!

[Bahbus · Accepted solution]

Your problem stems from the return value of SELECT(). Regardless of how you craft the expression, SELECT() always returns a list (even if its an empty list). So two options:

• If you positive that your current SELECT() expression always returns one correct result, you can wrap it in an ANY() expression.
• The cleaner, and probably better, method would be to switch to a LOOKUP() expression.

Either way should allow you to make the column a Ref.

[Luke_Vancleave]

The lookup expression works like a charm!