Linking Two Forms Through a Common Form Field and Updating Status

Question

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.

* 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).
* 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)* 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"
* 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_ · Answer

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

Projects

ProjectNameProject Nametext

ProjectStartDateProject Start Datedate

ProjectEndDateProject End Datedate

ProjectStatusProject StatusIF([entry.ProjectEndDate]>[entry._now],"Active","Completed")formula

EmployeeEmployee[189530697|ProjectManagers|EmployeeName|true||]smartselect

Projects

ProjectManagers

EmployeeNameEmployee Nametext

EmployeeIDEmployee IDnumber

EmployeeStatusEmployee StatusIF([entry.id.queryCount(Projects:@self;Employee:[entry.id] AND ProjectStatus:Active)]>0,"Busy","Available") formula

Project Managers

ProjectManagers

Hope it helps Alex

Wamiq Jamal · Answer

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_ · Answer

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