General Caster is here. Get rid of formula columns!

[rob (Roberto Tremonti)]

Thanks for your lovely feedback, @aholtzhauer .
That’s anyway something your should ask to monday.com.

[aholtzhauer (Andrew)]

I do have one formula question that I couldn’t seem to figure out… currently have a setup where there are two people columns (“Currently Working On” and “CAD Designer”), and wanted one column’s person to be casted to the other people column but only if a different columns status (“Current Stage”) contained something. I actually got it via:

IF(ISNUMBER(SEARCH("CAD",{project's Current Stage})),{project's CAD Designer})

However, if I ever take the CAD Designer column and unassign a person and not assign anyone, it doesn’t “assign no one” to the other column… so if someone was there before, they remain there. Is it possible to clear this out?

And in the opposing direction, how would one instead append a person to the column? I’m imagining something like:

IF(...,{project's Currently Working On}+{project's CAD Designer})

Not sure if “+” is the proper operator here. EDIT: Yeah should’ve thought this through better… can’t reference a column that I’m trying to cast a value to as it creates a loop. Still stuck on how to append a person to a people column though.

Thanks Rob!

[SteveinAU (Steve Canning)]

Hi @rob
Firstly I am very new to Monday so apologies for what maybe a rookie question
@george_planit asked a similar question on 24 June and I have reviewed the documentation to which you referred him.

My problem
I have items which have several subitems. The subitems have a formula cell calls “status point” that calculate subitem status “points” when the subitem status reaches “Done” using this formula

SWITCH({Name},“Detailing”,10,“AOS Checking”,100,“AOS Revision”,1000,“CFS Checking”,10000,“CFS Revision”,100000)*if({Status}=“Done”,1,0)

Subitems status points are summarised on the parent item and I want to use the total of these points in an automation to update the Parent status, which I cant do as Monday does not support summary fields in automation

So I have ben trying to use GC to cast the summary value into a number column that could then use in an automation

When I change the GC automation to reference a normal text or number field on the parent item the value is cast correctly by GC- For example {board name} works fine.

If I reference a summary field of a regular sub item number column ie {Job’s Subitems Numbers} (numbers being a vanilla number column in the sub item) it works fine

However the summary of a subitem formula field does not cast

I have tried SUBITEMS.SUM({Job’s Subitems Status Points}) and {Job’s Subitems Status Points}, but neither work.

Does GC work on summary field of formula columns or is this a limitation

Each of my sub items are sequential steps in a process and I want the parent status to reflect status of the most recent process- As I said I am a rookie so there maybe be a simple way to do this but if there is I cant find it- All and any help appreciated
Thanks
Steve

[rob (Roberto Tremonti)]

Hey @SteveinAU
The formula should work. Try and cast the result into a temp Text column (it accepts any value) to check what’s actually casted.
AFAICS you are using a custom automation/integration, that actually uses only the “action” part of GC. I guess it’s something new and available for some accounts only, as I can’t use it. cc @dipro

[basdebruin]

hi Rob,

It looks like this feature (using endpoint from apps in custom automations) is starting to roll out to specific accounts. I can see in my logfiles that some accounts are calling my app’s action endpoints without going through the subscribe endpoint (custom trigger). This causes a real headache.

Are you experiencing the same?

/Bas

[rob (Roberto Tremonti)]

Oooh, I didn’t know that, in this case, it skips the auth process. That’s a problem!
It can’t work without authorization.

[SteveinAU (Steve Canning)]

Hi @rob ,
Thanks for the reply
I found another method to achieve what I need without formula fields
When I first set up the automation the screen was yellow. I am not sure how I managed to get the integration automation over to the automations section (White). From memory it just appeared in the automations section. I deleted the automation when developed the new method and I have tried to recreate this morning but there is no “perform formula” option in automations so I cant see how I could have created that automation in the automation section- Beginners luck

So I set up this one now

Work Points is a summary column of a subitem formula column and DT Status is a text column.

The result of the automation is a blank in DT Status

If I modify the formula to select non formula summary column or non formula column it works as
expected
These are the fields I have available in GC to select

Note point budget and Point Budget and Points Bal are formula fields and not available in GC for selection in a formula

[rob (Roberto Tremonti)]

@SteveinAU

I have tried to recreate this morning but there is no “perform formula” option in automations so I cant see how I could have created that automation in the automation section

I guess the feature is in A/B testing, so it’s not available for all and could disappear.

@SteveinAU

If I modify the formula to select non formula summary column or non formula column it works as
expected

Formula columns are not supported because the value you see is calculated client-side and it’s not available as input in General Caster.

[elena]

@rob How can the formula ENCODEURL work with a column.
It only find that works with ENCODEURL(“hello hello”) but does not work with ENCODEURL("{item’s name}") or ENCODEURL({item’s name})

[rob (Roberto Tremonti)]

Hey @elena
ENCODEURL({item’s name}) should work.
Just make sure you are using the correct column reference by clicking on the corresponding column button at the bottom of formula field.

[elena]

Hi @rob no it does not work. I have tried lots of times, with lots of columns and doing it as you say.

[elena]

@rob
Results:

1. #value! when i use ENCODEURL({item’s Nombre completo mentor})
2. A6 when i use ENCODEURL("{item’s Nombre completo mentor}")

[rob (Roberto Tremonti)]

@elena
You don’t need to wrap the column reference between the double quotes, so the first version is correct.

ENCODEURL({item’s Nombre completo mentor})

ENCODEURL function requires a string as parameter, so I guess the raw value you pass is not accepted.
Just force the value to passed as string with one of the following options:

ENCODEURL(CONCATENATE("", {item’s Nombre completo mentor}))

or

ENCODEURL(TEXT({item’s Nombre completo mentor}, "0"))

[AdamCPU (Adam Raphael)]

@rob We want to be able to sort data from the timetracker data log, so that we can pull time logged this month, last november etc.

Is that something you can do with General Caster?

[rob (Roberto Tremonti)]

Hey @AdamCPU
Please explain your request.
General Cast can already retrieve summary data from a Time Tracking column, but logs are not supported at the moment.

[MitchellC (Mitchell Coppins)]

Hey @rob

I need to change a progress column based on a status column change. I currently have a mirrored column providing me with a value I need presented as the status column to drive the progress column.

I was wondering if there is any way General Caster can help?

[rob (Roberto Tremonti)]

As per monday.com documentation, Progress column cannot be updated by General Caster (and any other app).

[Skipper (Jonas)]

Hey @rob I need to push 2 days of “Target Finish Date” column if “target finish date” falls weekend.

For example if Today is 26th January , 2022 (wednesday) and Dead Line Days(number) is 4, the result is in “Target Finish Date” Column should be 1 February (Tuesday) 2022 and NOT 30 January (Sunday) 2022, pushing 2 days further because we don’t work on the weekends.

I tried this formula “RIGHT(LEFT(CONCATENATE(WORKDAY({Start Date}, ({Deadline Days}))), 15), 11)” IT works great couldn’t convert this function to general caster
Or this formula also doesn’t work on general caster WORKDAY({item’s Start Date}, {item’s Deadline Days})

Some how Target Finish Date(Date) returns “1 Jan, 1970” no matter what the “Start Date” is and not 1 February 2022

When I do debug with the Text it returns number like “44594” “44584” etc. I am thinking format issues but could you tell me where I am going to add days format so date column would understand as a date.

[Skipper (Jonas)]

Found it !
TEXT(WORKDAY(DATEVALUE({item’s Start Date}), {item’s Deadline Days}), “YYYY-MM-DD”)

[rob (Roberto Tremonti)]

Glad to know the formula works.
Yes, after your calculations, you need to convert a date value into the correct format in order to be accepted by a Date column.
There’s a section about it in GC documentation at https://generalcaster.app/website/documentation/formula/#date.