I think im doing it wrong; SELECT() Expression

[punkshift]

I have two tables, one is Assets, the other one is Asset Checkin Checkout.

I’m trying to change the value of the Status column from “Ready to Deploy” to “Deployed” under the Assets if a device has been checked out in the Asset Checkin Checkout sheet.

This is the expression I used under Referenced Rows: SELECT(Assets[Asset Tag #],[Asset Tag #]=[_THISROW])

It says it’s correct but when I’m trying to add a new entry in the Checkout form, it’s not changing the status. I already created the Bot for this

Hope you could help me with this!

[Aurelien]

Hi @punkshift

Welcome to the community !

If you are trying to get the children you just need to use your children list column.
Should be something like this:
[Related Assets]

[punkshift]

@Aurelien
Thanks for the welcome.

I don’t quite get what you’re suggesting. How do I use that?

Also, I am trying to do the instructions here: Changing Data from a Bot, Workflow Rule or Scheduled Report | AppSheet Help Center

If that will help.

[Aurelien · Accepted solution]

Hi @punkshift

Got it. I may have been too fast.

Let’s get it from the beginning, then
At explaining the [Related Assets] and REF_ROWS() expression, I realized my suggestion was incorrect. Please apologize my mistake.

About your request, now:

What attracts my attention is that this field expects a list of references. The “expression validator” just says the syntax is correct.

So, you may prefer:

SELECT(Assets[assetKeyColumn],
   [Asset Tag #] = [_THISROW].[Asset Tag #]
)

Also, you may prefer using FILTER instead of SELECT, as it will always returns a list of key_column values of the table.
That will give:

FILTER("Assets",
   [Asset Tag #] = [_THISROW].[Asset Tag #]
)

Then, about the context you give:
I have some questions regarding the way you proceed to achieve your goal.
This:

punkshift:

I’m trying to change the value of the Status column from “Ready to Deploy” to “Deployed” under the Assets if a device has been checked out in the Asset Checkin Checkout sheet.

Makes me think you have a status in order to know which are the assets that are checked in or out.
But, I don’t see any condition in your expression to do so.
You may rather want to use this expression instead:

FILTER("Assets",
  AND(
      [Asset Tag #]=[_THISROW].[Asset Tag #],
      [currentStatus]="Ready to Deploy",
      [checked_In]=true
   )
)

This:

punkshift:

when I’m trying to add a new entry in the Checkout form, it’s not changing the status

Not sure either what you mean. Is it a form based on a current asset that you wish to make status evolve ? Can you explain further ?

For reference:

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

How are FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() related? FILTER(), LOOKUP(), MAXROW(), and MINROW() are all built upon SELECT(), so they all share similar capabilities and limitations. FILTER("table", select-expression) is equivalent to: SELECT( table[key-column], (select-expression) ) LOOKUP(match-expression, "table", "match-column", "result-column") is equivalent to: ANY( SELECT( table[result-column], ([match-column] = (match-expression)) ) ) N…

help.appsheet.com

SELECT()

Gather column values from row in table or slice

help.appsheet.com

FILTER()

Select rows of table or slice

[punkshift]

@Aurelien
Thanks for the suggestions, I’ll try these.
Oh, and for this part:

punkshift:

when I’m trying to add a new entry in the Checkout form, it’s not changing the status

The Checkout form is another table. And when Im trying to add a new entry on this, the Status column in the Asset table doesn’t change even the Bot was triggered.

[Aurelien]

Hi @punkshift

punkshift:

The FILTER() worked like a charm!

Greeeeeaaaat !

punkshift:

And when Im trying to add a new entry on this, the Status column in the Asset table doesn’t change even the Bot was triggered

You may want to make another thread for that if you are still encountering trouble

[punkshift]

@Aurelien
The FILTER() worked like a charm!