How do I reference values from other macros in a Confiform Filter?

[Brian Robbins]

A continuation from this question.

A large portion of our workforce works an alternate schedule, where we have every other Friday off. My calculated InOrOut field expression now also checks the task assignment form for a person, then to see if today is Friday (or Monday in this case, so I can test :D). This works, and sets everyone OOO like it should. 

IF(ZEROIFEMPTY("[entry._func.queryCount(OOOEntries:@self;TechOOO:[entry.tech] AND OOODate.startDate:<=[entry._now] AND OOODate.endDate:>=[entry._now])]")>0 || ZEROIFEMPTY("[entry._func.queryCount(RASWB:@self;tech:[entry.tech] AND _today.formatDate(u):2)]")>0 , "no", "yes")

---

I also have another macro that calculates which Friday off is up this week. I have this done in 2 different macros, actually.

One in a Table Transformer with SQL:

SELECT FORMATDATE("today", "w") AS 'Current Week', 
CASE 
WHEN FORMATNUMBER(FORMATDATE("today", "w")) %2 = 1 THEN "A" 
ELSE "B" 
END AS 'Friday Off' 
FROM T1

One in a Table Spreadsheet as an excel function:

=IF(MOD(WEEKNUM(TODAY()),2)=1,"A","B")

How can I add this to my check for Out of Office? The goal is to have folks automatically set as unavailable if it is their Friday off. I know the expression takes the boolean OR operator (using the || notation).  Can I run that modulus calculation directly in the expression?

Okay, I've pivoted on this. The Friday calculation can wait, the In / Out field is more pressing. Fridays pose a different struggle, but I'll work through that after I solve the in/out times.

Here's what I have so far.

Time in and time out fields return an Epoch time with no date. Low number in the grand scheme of things. I figured out that there was a timezone difference / struggle with Epoch time and my StartTime and EndTime fields. I'm adding 25200000 to [entry._now] to cover the difference. This is calculated in my "NowCheck" field

I have a StartCheck and EndCheck field, which adds the [entry.StartTime] or [entry.EndTime] and [entry._today], appropriately. The field definition has a type of "time" so I only get the hours in MS from the beginning of Epoch time.

I also have a field that shows [entry._today] ... just for the sake of my sanity :) 

The field that shows if a tech is In or Out of office (TechInOut) is a formula (I need it calculated on every change) with the following expression

IF(ZEROIFEMPTY("[entry._func.queryCount(RASTechForm:@self;[entry.NowCheck]:>=[entry.StartCheck] AND [entry.NowCheck]: <= [entry.EndCheck])]")>0, "In", "Out")

The IF statement always evaluates to NA, setting everyone to "Out" and I can't figure out why. 

Here's an image of the timestamps in the fields. "Now" is between the start and end times, but still set to Out of office... :(

Full configuration below:

---

<h1>Add Tech To Board</h1>
<ac:structured-macro ac:macro-id="31258412-f9a8-40df-8080-5edd20192a4d" ac:name="confiform" ac:schema-version="1">
<ac:parameter ac:name="formName">RASTechForm</ac:parameter>
<ac:parameter ac:name="saveButtonLabel">Add</ac:parameter>
<ac:parameter ac:name="registrationFormTitle">Add new RAS Tech</ac:parameter>
<ac:rich-text-body>
<ac:structured-macro ac:macro-id="7edde19b-444b-49c1-84f2-b918e6ba56e9" ac:name="confiform-entry-register" ac:schema-version="1">
<ac:parameter ac:name="formName">RASTechForm</ac:parameter>
<ac:parameter ac:name="registrationButtonLabel">Add Tech</ac:parameter>
<ac:rich-text-body>
<br/>
</ac:rich-text-body>
</ac:structured-macro>
<p>
<br/>
</p>
<p>
<ac:structured-macro ac:macro-id="044d521c-a25a-4988-a267-cc65a608f295" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">Tech</ac:parameter>
<ac:parameter ac:name="fieldLabel">RAS Tech</ac:parameter>
<ac:parameter ac:name="type">user</ac:parameter>
<ac:parameter ac:name="required">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="b2cf0fef-e745-4d6e-8185-61ff3065827f" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">AssignTaskButton</ac:parameter>
<ac:parameter ac:name="fieldLabel">Assign Task</ac:parameter>
<ac:parameter ac:name="extras">RASTechLastAssigned=[entry._now]</ac:parameter>
<ac:parameter ac:name="type">action_button</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="2b48587f-42e0-4299-b8cb-9c7d399630be" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechSkips</ac:parameter>
<ac:parameter ac:name="fieldLabel">Skips</ac:parameter>
<ac:parameter ac:name="type">calc</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="dd6a013e-9f57-4ff6-8dfe-2617dae1f9b3" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechLastAssigned</ac:parameter>
<ac:parameter ac:name="fieldLabel">Last Assigned</ac:parameter>
<ac:parameter ac:name="type">datetime</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="2c22066e-1c43-4b77-864a-85d54fd441a9" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechStartTime</ac:parameter>
<ac:parameter ac:name="fieldLabel">Start Time</ac:parameter>
<ac:parameter ac:name="values">30</ac:parameter>
<ac:parameter ac:name="extras">HH:mm</ac:parameter>
<ac:parameter ac:name="extracontext">false</ac:parameter>
<ac:parameter ac:name="type">time</ac:parameter>
<ac:parameter ac:name="required">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="37c0e95a-0dc3-4c77-ace7-25fa53724afb" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechEndTime</ac:parameter>
<ac:parameter ac:name="fieldLabel">End Time</ac:parameter>
<ac:parameter ac:name="values">30</ac:parameter>
<ac:parameter ac:name="extras">HH:mm</ac:parameter>
<ac:parameter ac:name="extracontext">false</ac:parameter>
<ac:parameter ac:name="type">time</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="7fca9e18-6ca0-404c-bdf7-5dea56596944" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechFridayOff</ac:parameter>
<ac:parameter ac:name="fieldLabel">Friday Schedule</ac:parameter>
<ac:parameter ac:name="values">false[A=A|B=B|Off=4-10|]</ac:parameter>
<ac:parameter ac:name="type">radio_group</ac:parameter>
<ac:parameter ac:name="required">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="429e7b07-5460-4a69-8d36-1f6f8e3a5b36" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechFridayStartTime</ac:parameter>
<ac:parameter ac:name="fieldLabel">Friday Start Time</ac:parameter>
<ac:parameter ac:name="values">30</ac:parameter>
<ac:parameter ac:name="extras">HH:mm</ac:parameter>
<ac:parameter ac:name="type">time</ac:parameter>
<ac:parameter ac:name="required">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="8558985c-3091-40fa-b7b4-148579155758" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechFridayEndTime</ac:parameter>
<ac:parameter ac:name="fieldLabel">Friday End Time</ac:parameter>
<ac:parameter ac:name="values">30</ac:parameter>
<ac:parameter ac:name="extras">HH:mm</ac:parameter>
<ac:parameter ac:name="type">time</ac:parameter>
<ac:parameter ac:name="required">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="a7a14ee1-f678-4550-9f93-6410063d9df1" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechNotes</ac:parameter>
<ac:parameter ac:name="fieldLabel">Notes</ac:parameter>
<ac:parameter ac:name="type">text</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="2af17bd9-8ac9-45d5-8792-a0ec776127ec" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechInOut</ac:parameter>
<ac:parameter ac:name="fieldLabel">In or Out of Office</ac:parameter>
<ac:parameter ac:name="extras">IF(ZEROIFEMPTY("[entry._func.queryCount(RASTechForm:@self;[entry.NowCheck]:&gt;=[entry.StartCheck] AND [entry.NowCheck]: &lt;= [entry.EndCheck])]")&gt;0, "In", "Out")</ac:parameter>
<ac:parameter ac:name="type">formula</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="80e64a73-d154-4836-a5d2-c0daa2000987" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">NowCheck</ac:parameter>
<ac:parameter ac:name="fieldLabel">Now timestamp</ac:parameter>
<ac:parameter ac:name="extras">[entry._now]+25200000</ac:parameter>
<ac:parameter ac:name="type">formula</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="06c57e85-646c-4f62-96d0-e99973a19ab5" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">StartCheck</ac:parameter>
<ac:parameter ac:name="fieldLabel">Start Time + Today</ac:parameter>
<ac:parameter ac:name="extras">[entry.RASTechStartTime]+[entry._today]</ac:parameter>
<ac:parameter ac:name="type">formula</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="165601d7-76b2-4a7e-ae2b-4b3b21ef1423" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">EndCheck</ac:parameter>
<ac:parameter ac:name="fieldLabel">End Time</ac:parameter>
<ac:parameter ac:name="extras">[entry.RASTechEndTime]+[entry._today]</ac:parameter>
<ac:parameter ac:name="type">formula</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="5661baac-78b3-471e-ae2f-86059032d5d3" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">TodayCheck</ac:parameter>
<ac:parameter ac:name="fieldLabel">Today</ac:parameter>
<ac:parameter ac:name="extras">[entry._today]</ac:parameter>
<ac:parameter ac:name="type">formula</ac:parameter>
</ac:structured-macro>
</p>
<ac:structured-macro ac:macro-id="1dad4f5f-63a5-4fdf-925e-7a0d5e9c2093" ac:name="confiform-ifttt" ac:schema-version="1">
<ac:parameter ac:name="condition">hasChanged():true</ac:parameter>
<ac:parameter ac:name="action">Redirect to URL</ac:parameter>
<ac:parameter ac:name="event">onModified</ac:parameter>
<ac:parameter ac:name="title">@self</ac:parameter>
<ac:rich-text-body>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
</ac:rich-text-body>
</ac:structured-macro>
<p>
<br/>
</p>
<h1>Techs In-Office</h1>
<ac:structured-macro ac:macro-id="0313fd4f-fb7e-4c01-8d24-542ae3122e6c" ac:name="confiform-table" ac:schema-version="1">
<ac:parameter ac:name="filter">RASTechInOut:In</ac:parameter>
<ac:parameter ac:name="formName">RASTechForm</ac:parameter>
<ac:parameter ac:name="sort">RASTechLastAssigned ASC</ac:parameter>
<ac:parameter ac:name="enableGridEdit">true</ac:parameter>
<ac:rich-text-body>
<p>
<ac:structured-macro ac:macro-id="eb149d4a-9bec-4d1a-84cd-9e0c6bcfd3bd" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">Tech</ac:parameter>
<ac:parameter ac:name="withLabel">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="a387f7fb-3782-4f3d-8f91-406583b056f3" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">AssignTaskButton</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="6aff7f75-6207-483a-9bcc-1c5a50812f70" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechSkips</ac:parameter>
<ac:parameter ac:name="withLabel">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="339d273d-aa1f-422d-b17b-e81614cb10f3" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechStartTime</ac:parameter>
<ac:parameter ac:name="withLabel">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="14acbb8b-bbe6-4410-a01f-8616643b5036" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechEndTime</ac:parameter>
<ac:parameter ac:name="withLabel">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="6f55e462-4352-4a07-9a12-a104444d773b" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechFridayOff</ac:parameter>
<ac:parameter ac:name="withLabel">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="72fcdc64-4737-475e-835b-0715da26e578" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechFridayStartTime</ac:parameter>
<ac:parameter ac:name="withLabel">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="9637cd22-fcfe-4488-a554-1e6a53c4c734" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechFridayEndTime</ac:parameter>
<ac:parameter ac:name="withLabel">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="6a62868d-7ad3-4b20-829a-ef505b1de6ea" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechNotes</ac:parameter>
<ac:parameter ac:name="withLabel">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="703254ed-b090-43c7-99db-c9c606c35984" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechInOut</ac:parameter>
<ac:parameter ac:name="withLabel">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
<h1>Techs OOO</h1>
<ac:structured-macro ac:macro-id="b00ad794-c5e0-4fa8-9c10-e0aea6041db6" ac:name="confiform-table" ac:schema-version="1">
<ac:parameter ac:name="filter">!RASTechInOut:In</ac:parameter>
<ac:parameter ac:name="formName">RASTechForm</ac:parameter>
<ac:parameter ac:name="sort">LastAssigned ASC</ac:parameter>
<ac:parameter ac:name="enableGridEdit">true</ac:parameter>
<ac:rich-text-body>
<p>
<ac:structured-macro ac:macro-id="bccb0d72-3c80-4871-b296-b09372232c09" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">Tech</ac:parameter>
<ac:parameter ac:name="withLabel">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="9ad12624-f2dd-4534-b62f-859e4d5dc946" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">AssignTaskButton</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="5f2e2035-7159-4bc5-ba8f-b11f118faa84" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechSkips</ac:parameter>
<ac:parameter ac:name="withLabel">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="ee4085f4-cb4c-4fd1-bd8b-c8f7dc04b539" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechStartTime</ac:parameter>
<ac:parameter ac:name="withLabel">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="987f9780-b905-47b9-b65f-5e2019345326" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechEndTime</ac:parameter>
<ac:parameter ac:name="withLabel">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="f5b953e5-1e62-4e96-8e27-fc7e00863c15" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechFridayOff</ac:parameter>
<ac:parameter ac:name="withLabel">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="fbfc3b4f-7917-48e8-b0a9-cac306d6ba26" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechFridayStartTime</ac:parameter>
<ac:parameter ac:name="withLabel">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="1cf2c6b9-8048-4270-8eb8-a42b06e9b486" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechFridayEndTime</ac:parameter>
<ac:parameter ac:name="withLabel">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="126ce599-ca87-4758-bdcc-6979f9e95e35" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechNotes</ac:parameter>
<ac:parameter ac:name="withLabel">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="713ddcde-abf4-40a0-8c42-140333ff9832" ac:name="confiform-field" ac:schema-version="1">
<ac:parameter ac:name="fieldName">RASTechInOut</ac:parameter>
<ac:parameter ac:name="withLabel">true</ac:parameter>
</ac:structured-macro>
</p>
</ac:rich-text-body>
</ac:structured-macro>
<p>
<br/>
</p>
<p>
<br/>
</p>
<h1>OOO Entries</h1>
<p>
<br/>
</p>
<ac:structured-macro ac:macro-id="a5d51a5e-5b57-4b8b-8c34-1701ef316a6b" ac:name="confiform" ac:schema-version="1">
<ac:parameter ac:name="formName">OOOEntries</ac:parameter>
<ac:parameter ac:name="registrationFormTitle">Tech OOO Entry</ac:parameter>
<ac:rich-text-body>
<ac:structured-macro ac:macro-id="75f953ea-a5f4-4d4f-ade8-53b6a673643e" ac:name="confiform-entry-register" ac:schema-version="1">
<ac:parameter ac:name="pageTitle">
<ac:link>
<ri:page ri:content-title="@self"/>
</ac:link>
</ac:parameter>
<ac:parameter ac:name="formName">OOOEntries</ac:parameter>
<ac:parameter ac:name="registrationButtonLabel">Add OOO</ac:parameter>
<ac:rich-text-body>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
<p>
<br/>
</p>
<p>
<ac:structured-macro ac:macro-id="101042d0-ac18-44c4-a984-98785ca344c4" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">OOOTech</ac:parameter>
<ac:parameter ac:name="fieldLabel">Tech</ac:parameter>
<ac:parameter ac:name="type">user</ac:parameter>
<ac:parameter ac:name="required">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="92c3cd24-2535-4d67-ac86-15f7c6b16491" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">TechOOODate</ac:parameter>
<ac:parameter ac:name="fieldLabel">OOO Date Rage</ac:parameter>
<ac:parameter ac:name="values">30</ac:parameter>
<ac:parameter ac:name="type">datetimeinterval</ac:parameter>
<ac:parameter ac:name="required">true</ac:parameter>
</ac:structured-macro>
</p>
</ac:rich-text-body>
</ac:structured-macro>
<p>
<br/>
</p>
<p>
<br/>
</p>
<p>
<br/>
</p>

[Brian Robbins · Accepted solution]

I finally got it sorted. 
Removing the ZEROIFEMPTY and queryCount functions, the IF statement needs the Boolean AND operator (&&) instead of the word AND. 

This resolves correctly:

IF([entry.NowCheck] >= [entry.StartCheck] && [entry.NowCheck]<= [entry.EndCheck], "In", "Out")

[Alex Medved _ConfiForms_]

Is it so that you want to exclude ANY Friday? 

IF(ZEROIFEMPTY("[entry._func.queryCount(OOOEntries:@self;TechOOO:[entry.tech] AND OOODate.startDate:<=[entry._now] AND OOODate.endDate:>=[entry._now])]")>0 || ZEROIFEMPTY("[entry._func.queryCount(RASWB:@self;tech:[entry.tech] AND _today.formatDate(u):2)]")>0 || [entry._now.formatDate(u)] == 5, "no", "yes")

[Brian Robbins]

Once again I missed this reply! 

No, not every Friday. Every other Friday. That's why I was trying to work with the modulus operator and looking for a remainder when dividing the week number by 2. For instance, I have Friday off on odd-numbered weeks, and work on Friday on even-numbered weeks. 

And is there a character limit on the expression for this field? I ran into some "index out of bounds" errors but I wasn't sure if that was my input or a limitation on the field.

_today.formatDate(u):2

This was only to show I was working on this on a Tuesday, trying to make sure the function would work. :)

Additionally I'm working on setting the in/out based on current time versus start and end time. Dealing with Epoch time is driving me insane. Somehow,

 ._now (13:17) 

is less than the sum of

._today 

and a start time of 06:30.