... I don't really know how to title this question :))

[sorin_mihai]

Hello,

So, in a Task Management app, I have a Tasks Table, a Times Table and a Types of Production Table. So to any Task you can add Times you spent on that Task doing what kind of Type of Production. (On the “Sand the wood for the table”, I spent 2 hours - sanding)

So in the Task Deck View, there is an Action to Add Times. Clicking it takes you to the Add Times Form, where the name of the Task is already completed with a Deep Link formula in the Action button.
Then you have a dropdown of the Types of Production (like welding, sanding, cnc cutting etc.)

The thing is that sometimes, the Task name itself indicates what Type of production you can do while working on that Task. Like there would be a Task called “Sand the wood for the table” - it indicates that you will be sanding (as a type of production) while working to complete that task.

Question:

Is there a way to have the dropdown of Types of Production, automatically filled in with “Sanding” if the Task name I just clicked to get to the form, contains the text “Sand”? or have it automatically filled in with “CNC” if the Task name contains “CNC”? and so on…and still be able to change it if I actually did something else then CNC-ing on that Task!

---

I tried this formula

ifs(Contains([Task],“Sand”),“Sanding”)

in the Initial Value of the column [Types of Production] from the Times Table (this column is a Ref. column to the Types of production Table, which has also an image as an Icon … in case this is relevant). But it’s not working at all, it actually makes the whole dropdown not appear anymore in the Form (it’s definitely not well written)

…tried it in the LINKTOFORM() formula but … I can’t get it to work.

Any ideas?
it would really improve the UX of the app

Thank you,
Sorin

[MultiTech]

Your premise is sound, and the formula will work - it just sounds like you’re using the wrong value for the initial value.

You said the column that you’re trying to auto-set is a ref, but the value you’re setting things to is probly not the key-value for the “Sanding” ref record.

Am I correct in assuming the the Key-value would be something else? When working with a ref, you need to set values to the key-value, not the display value.

For instance, in this table you can see I’ve got a separate column for the ID and the Name

I’ve got another table with a reference connection to the table above, creating a parent-child relationship between them

When setting the initial value for the Character Ref, I wouldn’t use the name of the character - I would use the ID of the character. For an example, a hard-coded formula to set the ref would look something like this:

ifs(USEREMAIL() = "John@email.com", "IC6KDH9W")

NOT

ifs(USEREMAIL() = "John@email.com", "John")

[sorin_mihai]

Hy Matt,

So in the Types of Production Table, the key column is actually Types of Production (which is also a label along with an image column for icons).
And in the Times Table, the column Types of Production (the one I want to automate) is referencing the Types of Production Table, where “Sanding” is a value on the Key column of the Table. (which I wrote into the formula with copy paste to make sure it’s written correctly)

So I see your point, but it’s something else that is not working. However, I got excited knowing that it sounds like something doable

[Steve]

ANY(
  FILTER(
    "Types of Production",
    AND(
      ISNOTBLANK([Type name]),
      CONTAINS([Type name], [_THISROW].[Task name])
    )
  )
)

[sorin_mihai]

The formula is correct but it’s not working … and I can’t really follow it either to know if there something I should adapt. Can you explain it a bit please?

[Steve]

The idea with this expression is to gather a list of rows from the Types of Production table (FILTER("Types of Production", ...) that have a non-blank Type name column value (ISNOTBLANK([Type name]) and whose Type name column value contains the text of the form’s Task name column value (CONTAINS([Type name], [_THISROW].[Task name])). From that list of rows, return any one of them (ANY(...)), giving you an initial value of a reference to a row in Types of Production that has a Type name that contains the current Task name

[sorin_mihai]

Actually, I think I understand and it’s much more powerful and simpler than what I was thinking … but I can’t tell why it won’t work. I mean the expression builder says it’s fine but it doesn’t give any result in the app.

[sorin_mihai]

I do have a Valid If on the column as well if that has anything to do with it. The Valid If Filters the dropdown, depending on what Type of Project the Task is a part of…

[sorin_mihai]

Also, I don’t know why but in the Deck View of Times, I am getting triangles next to Task (which is used in the formula above and might be the cause of it not working). But the Task column in the Times Table, is simply a ref column to the Tasks Table … there is no formula no nothing … how come there is a broken reference here? this goes closer to what @MultiTech_Visions was saying earlier, but not for the Types of Production column …

[Steve]

One problem at a time.

[sorin_mihai]

Yes, thank you for the explanation
What will the formula display if the Filter gives back nothing (the Task name does not contain anything from any Types of Production)? Can it leave it simply empty if so?

I only said about the triangles because I thought they might be related because the triangle goes to the Task name column, which is in the formula - and the formula is correct but it doesn’t give results …

[Steve]

sorin_mihai:

What will the formula display if the Filter gives back nothing (the Task name does not contain anything from any Types of Production)? Can it leave it simply empty if so?

It will produce a blank result, so there will be no initial value.

[sorin_mihai · Accepted solution]

ANY(
FILTER(
“Types of Production”,
AND(
ISNOTBLANK([Type name]),
CONTAINS([Type name], [_THISROW].[Task name])
)
)
)

Hy @Steve
So I solved the triangles from the Task name, they are showing up perfectly now.
And your formula works too I just had to make one adjustment to my table:

The column Task from the Times table is a reference to the Tasks Table. In the views it displays the Task names, which is what I want to see, but when used in your formula (or any formula I guess), it holds the Key column value. So it couldn’t find “Sanding” in the Task Unique ID and so it always left the Type of Production initial value blank, like you said it would do if it wouldn’t find anything to match.
So I made another Virtual Column in the Times Table and called it Task Name, and dereference the Task from the Times Table to the Task Name column from the Task Table and then used this Virtual Column in your formula and it works perfectly

So really thank you so much
This is really great functionality and I wouldn’t have figured it out myself