Link to home
Start Free TrialLog in
Avatar of asubigsaxy

asked on

Create View dependant on field in response document

I have 3 forms:
Project (main form);ProjectDetails(response doc); event(response to response doc)

I have  created a view that simply shows main docs and all its response documents in a hierarchy.  What I need to do is show only the main form and its response documents if the project is Pending, which is in field "status" located on ProjectDetails form.  For the life of me, I can't figure this out.  Please let me know what other details I need to include.  Thanks!!!
Avatar of SysExpert
Flag of Israel image

this should be a sample of what goes in the view selection formula

SELECT Form = "Test" & STATUS != "ACCEPTED" & STATUS != "REJECTED" & @Date(@TextToTime(@Text(FollowUpDate))) = @Today;
Avatar of asubigsaxy


This is what I have for my selection formula, but it does need to be tweeked:

SELECT Form = "project" |  (Form = "Project" & status != "Complete") | Form = "event"

With this formula, I get ALL the main forms (which is what I don't want), and  I do get the pending projects and their events (this part is correct).  What do I need to  so that only main documents w/ an associated pending project are displayed in the view.
Below is a snipett for the documents returned with the above formula:
Name Phase    
All Saints Mission    
 All Saints Mission ,York Incomplete Project    
     Letter of Sanction issued
 All Saints Mission ,York Incomplete Project
*****The results below are also returned, but don't have an incomplete project associated w/it*****
Blessed Sacrament    
Blessed Trinity
Cathedral of St. John
Christ Our King
Christ the Divine Teacher
 ************************End Result*******************************

Thanks for your help!
SELECT Form = "project" |  (Form = "Project" & status != "Complete") | Form = "event"
Are "project"  and "Project" the same form ?

If yes then try :

SELECT Form = "event"  |  (Form = "Project" & status != "Complete")

I hope this helps !
Hi SysExpert,

The project & Project form are two different forms.  project = main form, Project = response form (its a typo, should be ProjectDetails).  I am so sorry for the confusion.

**A side note, I can actually only use the response form (ProjectDetails)& response to reponse form (event) to get the view I need.  My problem is categorizing it.  Thus, I did use the code above:
SELECT Form = "event"  |  (Form = "Project" & status != "Complete")
My only problem is that I couldn't get the  events to show properly in the view.  I ended up with the below result set:
Facility  Description  
     All Saints Mission ,York  

****The problem is here, I need the events to relate to the Project (in this case, All Saints Mission, York) *************
(Not Categorized)  
 Awaiting Bishop Approval  
 Letter of Sanction issued  
Avatar of SysExpert
Flag of Israel image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi asubigsaxy,
> What I need to do is show only the main form and its response documents
> if the project is Pending, which is in field "status" located on
> ProjectDetails form.
I'm afraid that is not possible. You have to do the selection on the main document. The best is to reflect the status in the main document.

Then it would be something like
    SELECT (Form = "Project" & status != "Complete") | @IsResponseDoc
If I may ask: what was your solution to the problem??
I am going to add a hidden status field on the main doc.  That seems like the easiest thing for me to do.
I wouldn't make it hidden, just computed, with as formula @ThisValue

The agent you will need only once. What's more important is the action at the end of the event document. Once in the event document the status is changed, and the event document is saved, that status should be reflected in the main document. Design with care...