Day of the week in table

[APTUS]

I understand we are not able to use the date formula for day of the week directly in a table field. I have managed to create a calculated field in a DataPage that uses a day of the week formula (see below for the formula used) which is working nicely and gives me the desired result on the DataPage. However, this is good for display only and I cannot use the result other than viewing it on the DataPage. 

Is there a way to pass the result from my DataPage calculated field back to a table (same table that the DataPage is connected to)? Or, is there anyway to get a working formula directly on the table that provides a number 1 through 7 for day of the week for a given date? 

The formula I am currently using on the DataPage and works well is:  DatePart(dw,[@field:XXXXX])

[MayMusic]

In the submission form where you add the values to the table, you can include an integer field, form element should be Calculated value  and use the same formula above

[APTUS]

Thank you so much for your response, MayMusic. Just before receiving your response, I figured out how to get day of the week automatically added in a table using triggered actions. Triggered actions offers some very nice date functions/features, etc.  However, if your suggested approach works, it might be more efficient and/or I would like to make it work since it may come in handy for other use cases.

So, i just tried following your instructions and I cannot get this formula to work in a form field.  Please see screenshot shown below for the set up in a dummy form and table I just created and I keep getting "invalid formula." You can see on the left had side that the field Weekday has been set up and an integer, I have made the form field a Calculated Value field, and I believe I have referenced the Date_Entered field properly in the formula.

Any help will be much appreciated.

Kaveh

[Franchiser]

@kvarjavand

 

I tried to replicate the issue from my end and I don't received error when verified. Can you try to remove the affected fields to use in this Web Form and hit save. Then, re-select them all and check?

 

[APTUS]

Franchiser, thanks for taking the time to look into this.  I see your test of this formula is being done in a DataPage and I had already been able to get it to work in a DataPage as well. The problem is that the same formula used directly in a Formula field in a table will not work. I was able to work around it by using a triggered action in the table and since triggered actions have a similar day of the week option, it works. However, the formula still will not work in a table field without a triggered action. Thanks again.

[DefinitelyNot31337]

On 8/6/2018 at 8:52 AM, kvarjavand said:

Is there a way to pass the result from my DataPage calculated field back to a table (same table that the DataPage is connected to)?

A: Yes,  I believe you can. I was able to extract the dayOfWeek and put in an Integer field on a Submission Form DataPage.

 

===

As for this one...

On 8/10/2018 at 10:18 AM, kvarjavand said:

The problem is that the same formula used directly in a Formula field in a table will not work

It's strange but I am also getting an error when I tried to put the exact same syntax on a formula field in the table.  I think it is best to contact Caspio about this issue.

 

Regards,

DN31337

[Vitalikssssss]

On 8/10/2018 at 8:18 PM, kvarjavand said:

Franchiser, thanks for taking the time to look into this.  I see your test of this formula is being done in a DataPage and I had already been able to get it to work in a DataPage as well. The problem is that the same formula used directly in a Formula field in a table will not work. I was able to work around it by using a triggered action in the table and since triggered actions have a similar day of the week option, it works. However, the formula still will not work in a table field without a triggered action. Thanks again.

Hi @kvarjavand,

Formula field doesn`t support Datepart(week) as far as I know.

You need to use a workaround solution from this post:

Hope this helps.

Regards,

vitalikssssss

[APTUS]

Thanks Vitalikssssss for sharing. The solution looks great and I will most likely use it in some other scenarios I may be needing. FYI given the limitation, back in August my workaround was to use triggered actions to determine day of the week and it has been working fine so far. I will be happy to share a screenshot of it if you or anyone else would like to see it.

[Texas]

On 10/25/2018 at 6:47 PM, kvarjavand said:

Thanks Vitalikssssss for sharing. The solution looks great and I will most likely use it in some other scenarios I may be needing. FYI given the limitation, back in August my workaround was to use triggered actions to determine day of the week and it has been working fine so far. I will be happy to share a screenshot of it if you or anyone else would like to see it.

Hello Kvarjavand,

Can you please share a screenshot how did you find day of the week using triggered action ?

Thanks

[MayMusic]

From Date (Purple Elements) get the third one which says "In <Date> GET <year>" and change year to weekday

[GoodBoy]

Hello! We can use triggered action to obtain the equivalent of a date in a week since we cannot use the weekday DatePart function in a formula field,

 

Here is a sample trigger setup.

[cheonsa]

Just wanted to share with you this link for more details about Triggered Actions: https://howto.caspio.com/tables-and-views/triggered-actions/

Also, if you already have records in the Table and if you would like to calculate or get the Day of the week for the existing records, you can use Application Tasks.

https://howto.caspio.com/tasks/

 

[Wikiwi]

On 8/12/2018 at 4:46 PM, DefinitelyNot31337 said:

A: Yes,  I believe you can. I was able to extract the dayOfWeek and put in an Integer field on a Submission Form DataPage.

 

===

As for this one...

It's strange but I am also getting an error when I tried to put the exact same syntax on a formula field in the table.  I think it is best to contact Caspio about this issue.

 

Regards,

DN31337

FYI, anything related to week for DataPart is not supported for formula fields. This is stated on their article for for different functions on formulas (https://howto.caspio.com/function-reference/#:~:text=Integer-,DatePart,-(datepart%2C date))

[Kronos]

Hi! adding this howto article in case it might help you:

https://howto.caspio.com/datapages/reports/advanced-reporting/calculated-fields-and-datediff-function/

[FredFarfadoe]

Hello all, I found an alternate way to determine the day of the week using default Caspio formulas with a little trick!

 

You can calculate the difference between the date field and a fixed reference date, and then find the remainder after division by 7 (using modulus). This would look like:

Datediff(day,'1950-1-1',[@field:DateFieldName])%7 + 1

I use 1/1/1950 in this example because it is a Sunday (and is a commonly used reference date). The result of the remainder would be somewhere from 0-6, 0 being a Sunday and 6 being Saturday. I add 1 to make this a 1-7 range (which I find easier to remember/use):

1 - Sunday

2 - Monday

3 - Tuesday

4 - Wednesday

5 - Thursday

6 - Friday

7 - Saturday

 

I hope this is useful to someone!