Inconsistent [_THIS.ROW] Behavior on Form submission based on DATE

[Sorweel]

In my budget app, when I want to add a new transaction I'm getting inconsistent behavior utilizing formula using data in the row I'm adding.

Scenario 1 : I use ACCOUNT NAME to automatically search and set ACCOUNT ID and ACCOUNT # automatically from a list and set them as Initial Value using SELECT and [_THIS.ROW].[ACCOUNT NAME].  Everything works as intended.  I can even switch Account Name in the form and Initial Values of ID and # update accordingly after form submission.

But when working with a DATE in the form, I cannot ever get it to recognize manual values I want to input.

Scenario 2:  I use DATE to set MONTH (formula calculating first day of the month from _THIS.ROW's DATE) and WEEK (formula calculating the date of the first day of the week from _THIS.ROW's DATE).

I get two completely different behaviors.  If I set the Initial Value of DATE to be blank, then MONTH is blank and WEEK is 1/1/70 after I submit the form no matter what I put in the DATE of the form.

If I set the initial value of DATE to be TODAY() or any other DATE, MONTH and WEEK are set correctly based on the DATE of TODAY() even though I am setting a completely different date in the DATE column in the submission form.

Why are the behaviors inconsistent with DATE?  I've even tried _THIS.ROW_AFTER and putting the formula in the "formula" and "Initial Value".  All results in Scenario 2 above.  I'd just like it to operate using the data I'm submitting in the form....Like it does with ACCOUNT NAME....

Any ideas why DATE is special?

[Marc_Dillon]

You really need to show the exact expressions that you're using.

[Sorweel]

This formula works as expected in "Initial Value" of the data:

Index((Select(Transactions[Account #], [Account] = [_THISROW].[Account])),1)

Yet, neither of these two formula below ever pick up the user input for the DATE data.  They will only ever reference whatever is set as "initial value" for the DATE.  No matter what I set for DATE in the form, I get a response using only what was the initial value.  If I open the item and save it again, it does it correctly if these exist in "formula", meaning the formula works, except that APPSHEET disregards the user input when initially setting DATE.

DATE(CONCATENATE(MONTH([_THISROW].[Date]),"/","1","/",YEAR([_THISROW].[Date])))

and

[_THISROW].[Date]-WEEKDAY([_THISROW].[Date])+1

[Marc_Dillon · Accepted solution]

You shouldn't be using THISROW in the bottom 2 expressions. It's only needed to disambiguate the context, like within a SELECT.

[Sorweel]

Thanks, that makes sense to me now.

[RedVox]

For those with English challenges,  "disambiguate"  = "provide".

Fun fact: the little-known and rarely-used term "disambiguate" means "to make easier to understand".  You can practically taste the irony!  

[Marc_Dillon]

"disambiguate"  = "provide".

 "disambiguate" means "to make easier to understand".

---

No they don't.

Ambiguous = when you cannot tell the difference between 2 or more things.

Disambiguate =  to make things not ambiguous, therefore, to make it possible to tell the difference between those 2 or more things.

[RedVox]

Disambiguate context = provide context