Action: Data: Execute Action on set of rows

[Deacon_Lawless]

I’ll jump right to it.

I’m a novice in excel and appsheet and it all seems like a bit much so maybe I can find some help here.

Currently I’m working on a project that needs to be able to track assets.
I’ve created some actions that will look at one table (table A), grab the data, and place it in the proper column of a separate table (table B).

My problem is in the action that gathers all the values in the selected column, then Concatenates the results.

The formula I’m currently using is TableA[Column]
This is certainly incorrect but I think its a start.

So my question is, how can I filter the results so that I only get the value assigned to my asset number?

[WillowMobileSys]

Welcome to the AppSheet Community!

Could you give a little contextual background as to what you are copying and why?

As a rule of thumb, you don’t really want to duplicate data within the app. This leads to issues where data gets out of sync and doesn’t match between tables.

Depending on what you are trying to accomplish, there may be easier ways to meet your goal.

[Deacon_Lawless]

So for context, say I have a driver taking one of my available assets from a pool of available assets.
I want to assign this contract number to the asset itself, that way I know where its supposed to be
as well as no longer available. The Contract numbers are almost keys that pull data from the spreadsheets as its the one thing everything has in common.

What do you think might work for something like this?

[WillowMobileSys]

Got it! Here’s what I would do.

Presume you have a Contracts table and an Assets table (you probably already do). In the Contracts table is your Contract Number and for arguments sake lets say that Contract Number is NOT the key. Instead you have a Contract ID column that is the unique key for each contract.

In your Assets table you would include a Contract column that is defined as “Ref” type against the Contracts table. This gives you a list of contracts to choose from when you enter the Asset record. Choosing one sets a reference to that contract row. In the data, the Contract ID as the link to the row that is written into the sheet.

With this setup, you can now access the Contract Number info in the Asset table by using “dot” notation. For example, you might create a Virtual column and set its App Formula to:

[Contract].[Contract Number]

The Virtual Column will be treated like a normal column anywhere you are in the app but is not written to the sheet.

If you did for some reason need to have the concrete value for Contract Number in the Asset sheet, you can add the “Contract Number” column and copy the value using the same “dot” notation expression above. I don’t recommend this.

[Deacon_Lawless]

I may have not explained myself properly, as creating a reference between 2 tables isn’t exactly what I’m looking for.

I’m trying to define this asset with the contract number. The goal being, any asset with a contract number will be excluded from available assets, as well as having a record of what assets are on which contracts.

I have to find a way to get the specific contract number
based on the asset number from my equipment sheet.

So all in all, my workflow rule says, whenever the status is marked ‘complete’,
grab the contract number for that dispatch order, and assign it to that specific asset.

The only problem is, the rule is grabbing all contract numbers within the ‘dispatch order’ table,
then concatenating the values in the exact cell I want the contract number to be in.

If you referring to the photo in my first post, what formula can i use to help me filter the results based on the Asset number?

• Thank you for all of your input

[WillowMobileSys · Accepted solution]

Ok, now that I see your data design, I don’t want to interfere with it to much.

Going back to your original post, your intent was to get the [Contract Number] from a table named Dispatch Order and place it into a column named [Contract Assigned] which I am seeing is in the Equipment List table.

Since you want to return a single value, the best expression to use in this case is a LOOKUP() function. There needs to be a way to “connect” the two tables with a common value. Because your Dispatch Order table also has the Asset Number on it then the expression you need is this:

LOOKUP([Asset Number], "Dispatch Order", "Asset Number", "ContractNo")

[Asset Number] refers to the column in the Equipment List row.

I am reading that you are using a Workflow rule and of course Actions to update the [Contract Assigned] column. You would simply place this expression in the specific Acton that updates that column.

[Deacon_Lawless]

Thanks for all of your help! I can finally move forward with this project!!

[Deacon_Lawless]

For reference, here is the table this data should be going into.
‘Contract Assigned’ is the target column. This is working due to a SELECT() funcion.
do you think the same function could be used to filter my data?