Linking Two Forms Through a Common Form Field and Updating Status

[Wamiq Jamal]

Hi Everyone!

I have been looking into building a tracking system through Confiforms. One form (Form A) holds all the details and information. One form (Form B) holds a numeric identifier and current status for each identifier. This identifier field holds constant values like an employee id and are to be assigned to records in Form A.

---

Form A - Projects

• Project Name (Text Block)
• Project Start Date (Date)
• Project End Date (Date)
• Project Status (Dropdown Values)
• Employee ID (Number, set up with smart value dropdown so that values from Form B show up, rules for field definition macro used to only show me available employees)

Form B - Project Managers

• Employee Name (Text Block)
• Employee ID (Number)
• Employee Status (Dropdown Values)

---

I am struggling with two tasks.

1. Set up Project Status in Form A to dynamically update to "completed" when the current date is greater than the end date. I have tried making Project Status a formula and pull NOW() to compare with the end date but ran into problems with the time zone (PST for me vs UTC for Confluence).
2. Set up both Form A and B so that it can make two dynamic updates. (Please note that Employee ID is always added to an existing Form A record and never at the time of record creation i.e. form submission)
   1. When Employee ID is updated in a Form A record, the Employee Status in Form B is updated to "busy" given that the Project Status in Form A is "active"
   2. When Project Status in Form A changes to "completed" for the specific record, the Employee status for the Employee ID being used in this record changes to "available".

Please let me know if you have any questions and provide more information about the form set up.

Thank you to everyone who responds in advance. =)

[Alex Medved _ConfiForms_]

Hi

What formula have you used for the  #1? And when it should really change the status - in PST or in UTC time?

For #2 - this is possible to do by setting up the rules to update records in the form B reacting on changes in form A

https://wiki.vertuna.com/display/CONFIFORMS/Configuring+ConfiForms+IFTTT+actions+and+rules#ConfiguringConfiFormsIFTTTactionsandrules-UpdateConfiFormsEntriesbyFilter

Alex

[Wamiq Jamal]

Hi Alex! Appreciate your support!

For the first problem, I tried using the formula below. I know there are two formulas that can format NOW() output but having trouble getting it to PST.

IF([entry.offerenddate]>NOW(),"Active","Completed")

As for the second one, would it be possible to some base level instructions on how to set this up? I did see this page and read through this but was unable to set it up to get the result I wanted.

Thanks!

[Alex Medved _ConfiForms_]

Hi @Wamiq Jamal 

Looking again at what you actually want to achieve I have to say that it is probably the best way to use the formulas in both forms (although, me personally, I would not design the form like that and rely on dates for status automation - life shows that this is never ideal and usually you need to change the statuses manually)

Anyway, little video and the storage format below for the reference (I am using a queryCount function from https://wiki.vertuna.com/display/CONFIFORMS/Virtual+functions)

Video

https://wiki.vertuna.com/download/attachments/178782502/Community%20-%20Linking%20Two%20Forms%20Through%20a%20Common%20Form%20Field%20and%20Updating%20Status.mov?api=v2

Storage format

<p><br /></p><ac:structured-macro ac:name="confiform" ac:schema-version="1" ac:macro-id="926f424b-2102-4b0f-8d0c-e4a7a587d400"><ac:parameter ac:name="formName">Projects</ac:parameter><ac:rich-text-body><ac:structured-macro ac:name="confiform-entry-register" ac:schema-version="1" ac:macro-id="78e7da12-f246-4688-85ac-036aae1ede5d"><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="5eaa8bdb-63e4-4c42-a7be-982e9ab7ba65"><ac:parameter ac:name="fieldName">ProjectName</ac:parameter><ac:parameter ac:name="fieldLabel">Project Name</ac:parameter><ac:parameter ac:name="type">text</ac:parameter></ac:structured-macro></p>
<p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="21253e39-bee9-409b-9c28-4aafee18212d"><ac:parameter ac:name="fieldName">ProjectStartDate</ac:parameter><ac:parameter ac:name="fieldLabel">Project Start Date</ac:parameter><ac:parameter ac:name="type">date</ac:parameter></ac:structured-macro></p>
<p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="56da2e7c-f2ca-4889-a035-26c36e3e3a97"><ac:parameter ac:name="fieldName">ProjectEndDate</ac:parameter><ac:parameter ac:name="fieldLabel">Project End Date</ac:parameter><ac:parameter ac:name="type">date</ac:parameter></ac:structured-macro></p>
<p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="e9207586-2232-4107-ae4a-5764ab40b58c"><ac:parameter ac:name="fieldName">ProjectStatus</ac:parameter><ac:parameter ac:name="fieldLabel">Project Status</ac:parameter><ac:parameter ac:name="extras">IF([entry.ProjectEndDate]&gt;[entry._now],&quot;Active&quot;,&quot;Completed&quot;)</ac:parameter><ac:parameter ac:name="type">formula</ac:parameter></ac:structured-macro></p>
<p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="d627ec36-f0d5-424a-9437-3be0a5a88e49"><ac:parameter ac:name="fieldName">Employee</ac:parameter><ac:parameter ac:name="fieldLabel">Employee</ac:parameter><ac:parameter ac:name="values">[189530697|ProjectManagers|EmployeeName|true||]</ac:parameter><ac:parameter ac:name="type">smartselect</ac:parameter></ac:structured-macro></p></ac:rich-text-body></ac:structured-macro>
<p>Projects</p><ac:structured-macro ac:name="confiform-table" ac:schema-version="1" ac:macro-id="41f3683a-f4e3-46cd-93ca-a9a89f737f34"><ac:parameter ac:name="formName">Projects</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="0f56f34b-790d-4834-9056-ca4830567a82"><ac:parameter ac:name="formName">ProjectManagers</ac:parameter><ac:rich-text-body><ac:structured-macro ac:name="confiform-entry-register" ac:schema-version="1" ac:macro-id="e63273b7-6022-412f-82d1-7d34e2b6b052"><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="2919ddab-61fc-456c-9425-4de16e9156f8"><ac:parameter ac:name="fieldName">EmployeeName</ac:parameter><ac:parameter ac:name="fieldLabel">Employee Name</ac:parameter><ac:parameter ac:name="type">text</ac:parameter></ac:structured-macro></p>
<p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="18605c03-c952-4752-b64f-6e493f1c9153"><ac:parameter ac:name="fieldName">EmployeeID</ac:parameter><ac:parameter ac:name="fieldLabel">Employee ID</ac:parameter><ac:parameter ac:name="type">number</ac:parameter></ac:structured-macro></p>
<p><ac:structured-macro ac:name="confiform-field-definition" ac:schema-version="1" ac:macro-id="1d520017-e8ec-4f00-a2a8-26ac90784db6"><ac:parameter ac:name="fieldName">EmployeeStatus</ac:parameter><ac:parameter ac:name="fieldLabel">Employee Status</ac:parameter><ac:parameter ac:name="extras">IF([entry.id.queryCount(Projects:@self;Employee:[entry.id] AND ProjectStatus:Active)]&gt;0,&quot;Busy&quot;,&quot;Available&quot;)   </ac:parameter><ac:parameter ac:name="type">formula</ac:parameter></ac:structured-macro></p></ac:rich-text-body></ac:structured-macro>
<p>Project Managers</p><ac:structured-macro ac:name="confiform-table" ac:schema-version="1" ac:macro-id="49b1245e-b104-4b4c-abe4-485e839b329b"><ac:parameter ac:name="formName">ProjectManagers</ac:parameter><ac:rich-text-body>
<p><br /></p></ac:rich-text-body></ac:structured-macro>
<p><br /></p>
<p><br /></p>

Hope it helps

Alex