How to set a value on another table

Hi!

I want to set a value on other table, using a value on this table.
Let say i want to set a value on Table1[Status]=Table2[Approve].

note :
Table 1 is reference with Table 2.
The value on [Status] is not reference with [Approve]

Is anyone can help what formula to use?
Thanks in advance

Hi!

This is relevant to what you want to do:

Even if you only want to write to one cell on another table, the “execute an action on a set or rows” is what you need to use.

1 Like

@Novianto_Djunaedi

I think we need more details.

Are you wanting to perform this update in a Form, with an Action or within a Workflow?

Also, as it might affect the answer, we probably should understand these statements below.

In what way is “Table 1 is reference with Table 2”? Is there a column in Table 2 that references Table 1 or the other way around, a column in Table 1 that references Table 2?

Which column is the Ref column? What is the data in the table being referenced?

In the second statement above I assume you mean that neither [Status] nor [Approve] are reference columns?


All this is important to know to provide you with the best solution. The answer may be as simple as using “dot” notation to pull the [Approve] value like `[Ref Column].[Approve]’. Or the solution may be as complex as creating a set of Actions to properly navigate the tables to then be able to assign [Status] the value of [Approve] using a SELECT() expression. It all depends on your mode of processing.

2 Likes

Thanks. I will try to learn this

Hi mate,

I will try to give example.
Table 1 column is
[Key]. Initial value = uniqueid()
[Status]. Initial value = “New Job”

Table 2 column is
[Ref Key]. Ref to table 1 [key]
[Approve] enum type. = “Approve”, “Reject”, “Pending”

Let say Table 1 is for create a transaction record. And table 2 is for approval. So i want every time a user submit value on [Approve]. The value will replace value on table 1 [status].

And when the status is “Pending”, then we want to submit again, the status on table 1 back to “new job” by using action / trigger on table 2.

Is it possible?
Thanks in advance

The simplest way to solve your updating problem is to move the [Approve] column into Table 1. Then you would have access to it and the [Status] column together to change as needed.


However, assuming that there are other reasons you have [Approve] in Table 2, I would try resolving the problem with an Action attached the Form Saved behavior of your Table 2 Form.

This Action, call it Action1, would be of type “execute an Action on a set of rows”. In the Reference Rows property use a FILTER() expression for Table 1 where [Key] = [_THISROW].[Ref Key]. The Referenced Action would Action2 described below.

Action2 operated on Table 1 and is of type “update some columns on this row”. This is where you set the [Status] column based on an expression. If [Approve] is “Approve” or “Reject”, then assign that value, else assign “New Job”. The expression might be like this:

IF(IN(LOOKUP(<value of approve from table 2>), LIST("Approve", "Reject")),
      LOOKUP(<value of approve from table 2>),
      "New Job"
  )

Obviously, all of the details are not there. I’ll assume you know how to fill them in. If not, just ask.

Doing it this way you would not need a separate action/workflow when the value of [Approve] is set to “Pending”. The Action above will take care of it.

2 Likes

hi mate,
Really appreciate for your response
i will try your solution after i’m back.
will update here if this working.
Thanks :slight_smile: