I have this SELECT formula. I want to add another form (named ‘Employee Profile’) to the formula so I can pull a field off the Employee Profile form into the current view. How do I modify this existing formula:
SELECT Form=“Exempt Timesheet”:“Timesheet” & Status != “NonExempt” & Weekend >= temp & Weekend <=@Today
to include the form Employee Profile?
I tried :
SELECT Form =“Employee Profile” | Form=“Exempt Timesheet”:“Timesheet” & Status != “NonExempt” & Weekend >= temp & Weekend <=@Today
but all I get is a blank column.
Thanks for any advice
Helen
Subject: Formula Help Please
Where are you putting this formula? “all I get is a blank column” makes me suspicious that you are tying to use it in a column. SELECT does not work in a column, you have to use it in the single View Selection formula for the view.
If you have put it in the correct place, I’m not sure you understand what you can do with it. The selection formula simply determines whether or not a document will be included in the view. When run against the document will the formula return true? If so the document is included. You can’t “pull a field off” one document and have it appear on the same row as another.
To answer the question a bit more directly, if you want to include all Employee Profile documents in the view, as well as the Exempt Timesheet and Timesheet documents, then you are pretty close. I’d put some parentheses in to make sure the logical opps where as desired.
SELECT (Form =“Employee Profile”) | (Form=“Exempt Timesheet”:“Timesheet” & Status != “NonExempt” & Weekend >= temp & Weekend <=@Today)
Subject: Formula Help Please
I’m guessing that you want to have rows of documents using the Timesheets, but want to have one of the columns pull data from a corresponding Profile.
Notes simply does not work that way - it’s not a relationship database, and you cannot pull data from multiple documents into one row in a view.
In a view, the only information you can show in the columns comes from the document in that row. Thus, as part of your database design, you’ll have to store the field you are interesting in right into the Timesheets. You could (for example) have some logic that runs as a Timesheet is being saved to get a value from the corresponding profile, and copy it onto a field in the Timesheet. You would then use that field in the view.
Subject: RE: Formula Help Please
Thanks Kerr and Graham for your responses.Yes. It seems like I was trying to do the impossible – and your explanation about what notes can’t do makes sense.
I’ll have to go with Plan B.
Thanks again
Helen.