Avatar of asubigsaxy
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!!!
Lotus IBM

Avatar of undefined
Last Comment
Sjef Bosman

8/22/2022 - Mon
SysExpert

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;
asubigsaxy

ASKER
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:
*******************RESULTS********************
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!
SysExpert

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 !
 
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
asubigsaxy

ASKER
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  
ASKER CERTIFIED SOLUTION
SysExpert

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sjef Bosman

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
Sjef Bosman

If I may ask: what was your solution to the problem??
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
asubigsaxy

ASKER
I am going to add a hidden status field on the main doc.  That seems like the easiest thing for me to do.
Sjef Bosman

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...