How to set a value on another table

[Novianto_Djunae]

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

[Kirk_Masden]

Hi!

This is relevant to what you want to do:

Features: Bulk Select vs. Data: execute an action on a set of rows Tips & Tricks 💡

In this weeks Feature Friday we released the top features of 2019. Two of the items included are often assumed to be the same feature but we wanted to break down them down to help understand what they are and how to use them. What are they? When you need to select more than one record at a time in your application you can use either of these features, but they do work in different ways. Bulk select is an action that allows you to scroll through the view highlighting the rows where you want to…

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.

[Novianto_Djunae]

Thanks. I will try to learn this

[WillowMobileSys]

@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.

Novianto_Djunaedi:

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

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.

[Novianto_Djunae]

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

[WillowMobileSys]

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.

[Novianto_Djunae]

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