Conditional initial value for ref column

[florjan]

Hi!

I have a working hours table with column that is ref to the customers table. What I want to achieve is to have form initial value (customer) based on data from workorders table, which contains customer column, start date column, and employee column.

So end result should be that when employee opens a form to enter working hours, there should already be customer selected that was on his workorder.

I tried but couldn't find a solution...  

[dbaum]

If you provide more information about the references between your tables and each table's columns, someone may be able to suggest an approach. I suggest post screen images showing the columns and column types for each table you mentioned, as well as an Employees table if your app includes that.

[florjan]

Hi!

As you suggested here are screen images of tables.  So I need to setup initail value of cutomer in working hours table to be the same as customer in workorder with status "in work" and employee same as one who is filling the form and start date is latest.

[dbaum]

Here's a draft to test and refine as necessary. It assumes that the Employee column in the Workorders table is a list of user emails since that's the type of value used in the corresponding column in the Working Hours table.

LOOKUP(
    MAXROW(
        "Workorders", 
        "Start Date", 
        AND(
            IN([_THISROW].[Employee], [Employee]), 
            [Status] = "in work"
        )
    ), 
    "Workorders", 
    "ID", 
    "Customer"
)

[florjan]

Is it even possible to set initial value for ref column?

[dbaum]

Yes.

[florjan]

Thank you! There is a customer selected but not the right one. I did change data structure a bit but this code should still be suitable. It ignores IN() expression which is quite interesting as AND() expression requires both to be true. It returns the workorder that is in work and has latest start date, but doesn't matter if there is an employee in there or not.

This is what I currently have

LOOKUP(
    MAXROW(
        "workorders", 
        "start date", 
        AND(
            IN([_THISROW].[employee], workorders[employees]), 
               [status of workorder] = "in work"
        )
    ), 
    "workorders", 
    "ID", 
    "ID"
)

---

@dbaum wrote:

Yes.

---

---

@dbaum wrote:

AND( IN([_THISROW].[Employee], [Employee]), [Status] = "in work" )

---

[florjan]

and also something strange happening. I have a slice Row filter condition that user can only see workorders where his name is included.

code

and(
    CONTAINS(
             [employees],
             lookup(
                    USERNAME(), "employee", "email", "name"
                    )
              ),
              OR([workorder status]="scheduled",[workorder status]="in work")
   )

 I tested it, signed in my account and adding my name to some workorders and It shows me workorders of some other employee.  I tested LOOKUP function alone and it returns the same employee name for all the rows. why is that?

[dbaum]

---

@florjan wrote:

It ignores IN() expression... doesn't matter if there is an employee in there or not.

---

That's because your IN function is evaluating whether the employee value appears in the employees column in any row of the workorders table--i.e., the list returned by "workorders[employees]". The expression I drafted for you evaluates only within one row at a time.

[florjan]

How should I fix the code?

[florjan]

Now it is a bit different. I have 2 tabels: working hours table and workorders table. In working hours form I need initial value of workorder that:

-has status in work

-has latest start date

-in column employees is employee name

I'm very close, only missing that last condition.

[dbaum]

---

@dbaum wrote:

Here's a draft to test and refine as necessary.

LOOKUP(
    MAXROW(
        "Workorders", 
        "Start Date", 
        AND(
            IN([_THISROW].[Employee], [Employee]), 
            [Status] = "in work"
        )
    ), 
    "Workorders", 
    "ID", 
    "Customer"
)

---

[florjan]

I still can't get it to work. I think that the code should return workorder ID in order to show initial value. I'm also getting error that parameter 2 of function IN in wrong type if exclude workorders in front of [employee]. I don't know how to make expression evaluate only within one row at a time as you said, not in any row.

[dbaum · Accepted solution]

---

@florjan wrote:

parameter 2 of function IN in wrong type

---

That parameter must be a list. In a prior screenshot that you provided, that column was indeed a list. In your latest screenshot, it's now only text.

---

@florjan wrote:

the code should return workorder ID

---

I interpreted your prior posts to mean that you wanted the value to be from the customer column. If instead you only want the workorder ID column's value, exclude the LOOKUP function from the expression I drafted.

[florjan]

Thank you so much! That solved my problem