Formula Question

Michele Thompson 2

Good morning! 


I am very thankful for this community. It has been a big help!


I made a report for our events. I did the report to add participants, guests, etc. My question is this:  I want to put Guest of... if a participant is a guest of someone. I can't figure out the right formula or where to actuallyput the field so that it puts Guest of (name) when there is someone that is a guest, and then it does not print anything if they aren't a guest. Thoughts? 




Thank you!!!!

Michele

Ryan Hyde

What's the end product you're trying to create? An actual printed list, a spreadsheet for mail merge?


I just took a look at the Participant type export, and I found that the "gues of" field is right where you'd expect it to be. So what I would do is output all the information you need including the 'guest of' field, and then when you export to an Excel doc, add a new column before the "guest of" column, sort by the "guest of" column (to push all populated fields to the top), and then just populate the words "guest of" in any cell adjacent to a populated cell. 


You could either print this directly if that's what you want, or you could include those two columns in your mail merge if that's your use case. 

James Andrews

Michele Thompson:

Good morning! 


I am very thankful for this community. It has been a big help!


I made a report for our events. I did the report to add participants, guests, etc. My question is this:  I want to put Guest of... if a participant is a guest of someone. I can't figure out the right formula or where to actuallyput the field so that it puts Guest of (name) when there is someone that is a guest, and then it does not print anything if they aren't a guest. Thoughts? 




Thank you!!!!

Michele

Put the label and the field {Prt_Guest_Of} in the report, then right click the label and choose format field. Click the formula button next to suppress on the common tab.  In that formula box put in


Isnull({Prt_Guest_Of}) or {Prt_Guest_Of} = ""


And hit OK.  That will suppress your label saying Guest of: if there's no info in the guest of field. You don't need to do it for the guest of field itself.

Michele Thompson 2

Thank you! This worked!!! My one other
question, is it possible to get the words Guest of: to print before
the name but only show up if there is a guest?

 

Thank you again!!!

 

Michele Thompson

Database
Administrator

 

We Connect You. We Support
You.

We Strengthen the Long Blue Line. We
Give Back.

47 Mohegan Avenue, New London, CT 06320

860-442-2683 x22; www.cgaalumni.org

 

Marci Makin

Michele Thompson:

 

Thank you! This worked!!! My one other question, is it possible to get the words Guest of: to print before the name but only show up if there is a guest?

 

Thank you again!!!

 

Michele Thompson

Database Administrator

 

 

We Connect You. We Support You.

We Strengthen the Long Blue Line. We Give Back.

47 Mohegan Avenue, New London, CT 06320

860-442-2683 x22; www.cgaalumni.org

 

Michele - You may have already figured this out, but the label he was describing would be a text field that says Guest of and that is the field that you would suppress if Isnull({Prt_Guest_Of}) or {Prt_Guest_Of} = "".  I hope this helps.


Marci

 

Michele Thompson 2

Hi
Marci!

 

Thank
you, I did not know that. And it worked, too! Thank you for the
tip! So happy to have it work properly now.

 

 

Michele
Thompson

Database
Administrator

 

We
Connect You. We Support You.

We
Strengthen the Long Blue Line. We Give Back.

47
Mohegan Avenue, New London, CT 06320

860-442-2683
x22; www.cgaalumni.org