How can I set a value in one Confiform based on dates in another Confiform?

[Brian Robbins]

I'm using a pair of Confiforms that I'm using as a task assignment application. One form shows who is available to receive task assignments. This form is sorted Ascending based on a 'last assigned' field refreshes after task assignment, keeping a nice orderly flow for tasks to be assigned. There are fields for user, last assigned date, and an in-office flag. I use the in-office flag to filter 

The 2nd form holds out of office entries. I need this second form for a historical record of the OOO entries. There's a user field, start date, and end date. 

My desired outcome is to have the OOO entries set the In-Office flag in the task assignment form for a specific user, comparing today's date against the start and end dates.

I would need to match the user in each form, check that today's date is both greater than the start OOO date and less than the end OOO date, then change the in-office flag in another form to 'no' 

I hope that makes sense :) Thanks in advance!

[Alex Medved _ConfiForms_ · Accepted solution]

Hi @Brian Robbins 

The problem here is not how to set the value, but WHEN to update it

I would try to solve this with a formula field...

Little video 

And the configuration for the reference (in case you need it)

<ac:structured-macro ac:name="confiform" ac:schema-version="1" ac:macro-id="f00046eb-582e-421b-ae84-1a8e0b1b10c2"><ac:parameter ac:name="formName">tasks</ac:parameter><ac:rich-text-body><ac:structured-macro ac:name="confiform-entry-register" ac:schema-version="1" ac:macro-id="b4ad017a-64b3-45bc-9f52-62c264db49ed"><ac:rich-text-body><p><br /></p></ac:rich-text-body></ac:structured-macro><p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="465fb902-926e-4be4-83b7-d526461125c3"><ac:parameter ac:name="fieldName">user</ac:parameter><ac:parameter ac:name="fieldLabel">User</ac:parameter><ac:parameter ac:name="type">user</ac:parameter></ac:structured-macro></p><p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="f746017f-ed47-4bba-8ad0-c3b17b2098b3"><ac:parameter ac:name="fieldName">oof</ac:parameter><ac:parameter ac:name="fieldLabel">oof</ac:parameter><ac:parameter ac:name="extras">IF(ZEROIFEMPTY(&quot;[entry._func.queryCount(oof:@self;user:[entry.user] AND oofTime.startDate:&lt;=[entry._now] AND oofTime.endDate:&gt;=[entry._now])]&quot;) &gt; 0, &quot;Yes&quot;, &quot;No&quot;)</ac:parameter><ac:parameter ac:name="type">formula</ac:parameter></ac:structured-macro></p></ac:rich-text-body></ac:structured-macro><p><br /></p><p>Tasks</p><ac:structured-macro ac:name="confiform-table" ac:schema-version="1" ac:macro-id="eed73b44-86fb-48f8-94af-7896f0780d44"><ac:parameter ac:name="formName">tasks</ac:parameter><ac:rich-text-body><p><br /></p></ac:rich-text-body></ac:structured-macro><p><br /></p><p>OOF times</p><ac:structured-macro ac:name="confiform-table" ac:schema-version="1" ac:macro-id="ef8d333d-d6da-4e75-8f13-fcd260ecd2e0"><ac:parameter ac:name="formName">oof</ac:parameter><ac:rich-text-body><p><br /></p></ac:rich-text-body></ac:structured-macro><p><br /></p><ac:structured-macro ac:name="confiform" ac:schema-version="1" ac:macro-id="52610bb9-1a78-4460-916e-9f6f03a89aab"><ac:parameter ac:name="formName">oof</ac:parameter><ac:rich-text-body><ac:structured-macro ac:name="confiform-entry-register" ac:schema-version="1" ac:macro-id="c9c5628d-8c8a-4439-b7be-87a68a414ce2"><ac:rich-text-body><p><br /></p></ac:rich-text-body></ac:structured-macro><p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="ce3d8d1f-4aa3-484e-a86c-225c287fed99"><ac:parameter ac:name="fieldName">user</ac:parameter><ac:parameter ac:name="fieldLabel">User</ac:parameter><ac:parameter ac:name="type">user</ac:parameter></ac:structured-macro></p><p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="943991f9-54dd-4168-b95f-6bbe755b9fbc"><ac:parameter ac:name="fieldName">oofTime</ac:parameter><ac:parameter ac:name="fieldLabel">OOF time</ac:parameter><ac:parameter ac:name="type">datetimeinterval</ac:parameter></ac:structured-macro></p></ac:rich-text-body></ac:structured-macro>

Alex

[Brian Robbins]

Thank you very much for the video, that's extremely helpful! Is the code block in the video to clearly display what goes in the expression of the formula field? I assume so, given that it is not part of the configuration you provided :) 

I'm struggling to get this to function. I had separate fields for start/end date and those weren't being picked up. I changed to a single DateTime interval like you have in the demo and it still isn't setting things correctly. I have three entries in my person form for task assignment. The function is setting everyone to "in office". 

Here's the code configuration:

<ac:structured-macro ac:macro-id="352021c5-6bf5-48c4-a465-f21f105c70f6" ac:name="confiform-entry-register" ac:schema-version="1">
<ac:parameter ac:name="formName">RASWB</ac:parameter>
<ac:parameter ac:name="registrationButtonLabel">Add Tech</ac:parameter>
<ac:rich-text-body>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
<ac:structured-macro ac:macro-id="15621650-bd6f-4e91-b1fe-cfcec53f7604" ac:name="confiform" ac:schema-version="1">
<ac:parameter ac:name="formName">RASWB</ac:parameter>
<ac:parameter ac:name="registrationFormTitle">RAS WhiteBoard</ac:parameter>
<ac:rich-text-body>
<p>
<ac:structured-macro ac:macro-id="0bc3f38f-e929-4035-9338-764fef61fe61" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">tech</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="c5894bda-c308-4cf3-a9e6-0227525fe393" 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="values">Assign Task</ac:parameter>
<ac:parameter ac:name="extras">LastAssigned=[now]</ac:parameter>
<ac:parameter ac:name="type">action_button</ac:parameter>
<ac:parameter ac:name="separator">Task Assigned</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="a85d386a-e407-4440-8c27-6bea4e8a9f9b" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">LastAssigned</ac:parameter>
<ac:parameter ac:name="fieldLabel">LastAssigned</ac:parameter>
<ac:parameter ac:name="type">datetime</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="67e8e683-cda5-47c8-81b3-af7a7f27b490" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">TechSchedule</ac:parameter>
<ac:parameter ac:name="fieldLabel">Schedule</ac:parameter>
<ac:parameter ac:name="values">false[open=Open|close=Close|custom=Custom|]</ac:parameter>
<ac:parameter ac:name="fieldDescription">EX: 9/80 A, S8, 4/10</ac:parameter>
<ac:parameter ac:name="type">select</ac:parameter>
<ac:parameter ac:name="required">true</ac:parameter>
</ac:structured-macro>
</p>
<p>
<ac:structured-macro ac:macro-id="3dfcb909-df60-421e-ad68-0901c3f13833" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">InOrOut</ac:parameter>
<ac:parameter ac:name="fieldLabel">In Office</ac:parameter>
<ac:parameter ac:name="extras">IF(ZEROIFEMPTY("[entry._func.queryCount(OOOEntries:@self;user:[entry.user] AND OOODate.startDate:&lt;=[entry._now] AND OOODate.endDate:&gt;=[entry._now])]")&gt;0, "no", "yes")</ac:parameter>
<ac:parameter ac:name="type">formula</ac:parameter>
</ac:structured-macro>
</p>
<ac:structured-macro ac:macro-id="a14b9dbd-02e7-4b9c-a365-c8cc0b662b3e" 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>
<br/>
</ac:rich-text-body>
</ac:structured-macro>
<p>
<br/>
</p>
<ac:structured-macro ac:macro-id="2162dc74-9940-4fef-90f9-1d0cd8a6353c" ac:name="code" ac:schema-version="1">
<ac:plain-text-body><![CDATA[IF(ZEROIFEMPTY("[entry._func.queryCount(OOOEntries:@self;user:[entry.user] AND OOODate.startDate:<=[entry._now] AND OOODate.endDate:>=[entry._now])]")>0, "no", "yes")]]></ac:plain-text-body>
</ac:structured-macro>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
<p>
<br/>
</p>
<p>
<br/>
</p>
<h1>Techs In-Office</h1>
<ac:structured-macro ac:macro-id="ae0cf3c4-50ef-4217-a2d9-b091ea96b60c" ac:name="confiform-table" ac:schema-version="1">
<ac:parameter ac:name="filter">InOrOut:yes</ac:parameter>
<ac:parameter ac:name="formName">RASWB</ac:parameter>
<ac:parameter ac:name="sort">LastAssigned ASC</ac:parameter>
<ac:parameter ac:name="enableGridEdit">true</ac:parameter>
<ac:rich-text-body>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
<h1>Techs OOO</h1>
<ac:structured-macro ac:macro-id="c9f9636c-5281-4be1-88b5-ac920f852c68" ac:name="confiform-table" ac:schema-version="1">
<ac:parameter ac:name="filter">!InOrOut:yes</ac:parameter>
<ac:parameter ac:name="formName">RASWB</ac:parameter>
<ac:parameter ac:name="sort">LastAssigned ASC</ac:parameter>
<ac:parameter ac:name="enableGridEdit">true</ac:parameter>
<ac:rich-text-body>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
<p>
<br/>
</p>
<h1>OOO Entries</h1>
<ac:structured-macro ac:macro-id="f8082ccc-3db3-4b02-b379-1f6fd7e85ac8" ac:name="confiform-entry-register" ac:schema-version="1">
<ac:parameter ac:name="formName">OOOEntries</ac:parameter>
<ac:parameter ac:name="registrationButtonLabel">Enter OOO</ac:parameter>
<ac:rich-text-body>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>
<ac:structured-macro ac:macro-id="0c39c360-7c6b-438f-b1bd-3b7d9e60e573" ac:name="confiform" ac:schema-version="1">
<ac:parameter ac:name="formName">OOOEntries</ac:parameter>
<ac:rich-text-body>
<p>
<ac:structured-macro ac:macro-id="ca1cf002-3249-4185-b6cc-fe18d731de56" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">TechOOO</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="e4bdcc1e-9ef4-4a43-9d2e-6586f8b870a8" ac:name="confiform-field-definition" ac:schema-version="1">
<ac:parameter ac:name="fieldName">OOODate</ac:parameter>
<ac:parameter ac:name="fieldLabel">OOO Date Range</ac:parameter>
<ac:parameter ac:name="values">15</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>
<ac:structured-macro ac:macro-id="51389308-d659-4950-9884-d495f5c9a7a2" ac:name="confiform-table" ac:schema-version="1">
<ac:parameter ac:name="formName">OOOEntries</ac:parameter>
<ac:rich-text-body>
<p>
<br/>
</p>
</ac:rich-text-body>
</ac:structured-macro>

[Alex Medved _ConfiForms_]

Fixed your configuration

<ac:structured-macro ac:name="confiform-entry-register" ac:schema-version="1" ac:macro-id="352021c5-6bf5-48c4-a465-f21f105c70f6"><ac:parameter ac:name="formName">RASWB</ac:parameter><ac:parameter ac:name="registrationButtonLabel">Add Tech</ac:parameter><ac:rich-text-body><p><br /></p></ac:rich-text-body></ac:structured-macro><ac:structured-macro ac:name="confiform" ac:schema-version="1" ac:macro-id="15621650-bd6f-4e91-b1fe-cfcec53f7604"><ac:parameter ac:name="formName">RASWB</ac:parameter><ac:parameter ac:name="registrationFormTitle">RAS WhiteBoard</ac:parameter><ac:rich-text-body><p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="0bc3f38f-e929-4035-9338-764fef61fe61"><ac:parameter ac:name="fieldName">tech</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:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="919b3461-fe8f-4e48-a106-bda1490fe047"><ac:parameter ac:name="fieldName">AssignTaskButton</ac:parameter><ac:parameter ac:name="fieldLabel">Assign Task</ac:parameter><ac:parameter ac:name="values">Assign Task</ac:parameter><ac:parameter ac:name="extras">LastAssigned=[entry._now]</ac:parameter><ac:parameter ac:name="type">action_button</ac:parameter><ac:parameter ac:name="separator">Task Assigned</ac:parameter></ac:structured-macro></p><p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="a85d386a-e407-4440-8c27-6bea4e8a9f9b"><ac:parameter ac:name="fieldName">LastAssigned</ac:parameter><ac:parameter ac:name="fieldLabel">LastAssigned</ac:parameter><ac:parameter ac:name="type">datetime</ac:parameter></ac:structured-macro></p><p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="67e8e683-cda5-47c8-81b3-af7a7f27b490"><ac:parameter ac:name="fieldName">TechSchedule</ac:parameter><ac:parameter ac:name="fieldLabel">Schedule</ac:parameter><ac:parameter ac:name="values">false[open=Open|close=Close|custom=Custom|]</ac:parameter><ac:parameter ac:name="fieldDescription">EX: 9/80 A, S8, 4/10</ac:parameter><ac:parameter ac:name="type">select</ac:parameter><ac:parameter ac:name="required">true</ac:parameter></ac:structured-macro></p><p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="42cb7180-6e46-4a5b-a06b-d6b0f4afbea9"><ac:parameter ac:name="fieldName">InOrOut</ac:parameter><ac:parameter ac:name="fieldLabel">In Office</ac:parameter><ac:parameter ac:name="extras">IF(ZEROIFEMPTY(&quot;[entry._func.queryCount(OOOEntries:@self;TechOOO:[entry.tech] AND OOODate.startDate:&lt;=[entry._now] AND OOODate.endDate:&gt;=[entry._now])]&quot;)&gt;0, &quot;no&quot;, &quot;yes&quot;)</ac:parameter><ac:parameter ac:name="type">formula</ac:parameter></ac:structured-macro></p><ac:structured-macro ac:name="confiform-ifttt" ac:schema-version="1" ac:macro-id="a14b9dbd-02e7-4b9c-a365-c8cc0b662b3e"><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><br /></ac:rich-text-body></ac:structured-macro><p><br /></p></ac:rich-text-body></ac:structured-macro><h1>Techs In-Office</h1><ac:structured-macro ac:name="confiform-table" ac:schema-version="1" ac:macro-id="ae0cf3c4-50ef-4217-a2d9-b091ea96b60c"><ac:parameter ac:name="filter">InOrOut:yes</ac:parameter><ac:parameter ac:name="formName">RASWB</ac:parameter><ac:parameter ac:name="sort">LastAssigned ASC</ac:parameter><ac:parameter ac:name="enableGridEdit">true</ac:parameter><ac:rich-text-body><p><br /></p></ac:rich-text-body></ac:structured-macro><h1>Techs OOO</h1><ac:structured-macro ac:name="confiform-table" ac:schema-version="1" ac:macro-id="c9f9636c-5281-4be1-88b5-ac920f852c68"><ac:parameter ac:name="filter">!InOrOut:yes</ac:parameter><ac:parameter ac:name="formName">RASWB</ac:parameter><ac:parameter ac:name="sort">LastAssigned ASC</ac:parameter><ac:parameter ac:name="enableGridEdit">true</ac:parameter><ac:rich-text-body><p><br /></p></ac:rich-text-body></ac:structured-macro><p><br /></p><h1>OOO Entries</h1><ac:structured-macro ac:name="confiform-entry-register" ac:schema-version="1" ac:macro-id="f8082ccc-3db3-4b02-b379-1f6fd7e85ac8"><ac:parameter ac:name="formName">OOOEntries</ac:parameter><ac:parameter ac:name="registrationButtonLabel">Enter OOO</ac:parameter><ac:rich-text-body><p><br /></p></ac:rich-text-body></ac:structured-macro><ac:structured-macro ac:name="confiform" ac:schema-version="1" ac:macro-id="0c39c360-7c6b-438f-b1bd-3b7d9e60e573"><ac:parameter ac:name="formName">OOOEntries</ac:parameter><ac:rich-text-body><p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="ca1cf002-3249-4185-b6cc-fe18d731de56"><ac:parameter ac:name="fieldName">TechOOO</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:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="e4bdcc1e-9ef4-4a43-9d2e-6586f8b870a8"><ac:parameter ac:name="fieldName">OOODate</ac:parameter><ac:parameter ac:name="fieldLabel">OOO Date Range</ac:parameter><ac:parameter ac:name="values">15</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><ac:structured-macro ac:name="confiform-table" ac:schema-version="1" ac:macro-id="51389308-d659-4950-9884-d495f5c9a7a2"><ac:parameter ac:name="formName">OOOEntries</ac:parameter><ac:rich-text-body><p><br /></p></ac:rich-text-body></ac:structured-macro>

[Brian Robbins]

Alex,

Thank you so very much. I was not properly referencing the person field in my 2nd form. Much appreciated!!!

[Alex Medved _ConfiForms_]

I have actually the same error in the video ;) checking against the current user, not the selected one

Sorry about that