Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-10-2019, 01:55 PM
shawn.low@cox.net shawn.low@cox.net is offline "IF" Staement(s) not Working properly Windows 10 "IF" Staement(s) not Working properly Office 2016
Novice
"IF" Staement(s) not Working properly
 
Join Date: May 2019
Location: Aliso Viejo, CA.
Posts: 24
shawn.low@cox.net is on a distinguished road
Default "IF" Staement(s) not Working properly

Good Afternoon,


Please let em apologize in advance for my lack of programming and coding knowledge.


I am currently working on a Word Document that needs to be created "Dynamically". We have a "Master" document that has all of the paragraphs and phrases that could be used in the final version of the document.


My challenge is to add in logic that our 3rd party application (Conga Composer) can take and work with. There are Merge Fields throughout the document so that data from our CRM (Salesforce) flows into the document where needed.

I need to use several "IF" statements throughout the document because, depending on the "State" the recipient lives in (or some other criteria) will determine which paragraph from the Master document will be added or not. (A person living in CA would not need to see any information regarding state laws in South Carolina)


This is what my "IF" statement looks like right now (it's actually 4 different "IF" statements)


{ IF "{{OPPORTUNITY_AFFILIATE_ID}}" = 4418 "Payday Loan, LLC dba Payday Money Centers, with its headquarters at 300 S. Harbor Blvd., Suite 500, Anaheim, California 92805, California Finance Lender License No. 603 6712 (referred to herein as "Broker") is acting as a broker to assist LoanMe, Inc., 1900 S. State College Blvd., Suite 300, Anaheim, CA 92806, California Finance Lender License No. 603K061 ("Lender") in making your loan. By signing below, you acknowledge that you understand:
1. Broker is acting as a loan broker to help Lender make your loan. This assistance to Lender may include obtaining information and documents necessary for Lender to make your loan. 2. Broker will receive $300 from the Lender as compensation for brokering your loan.


2. Broker will receive $750 from the Lender as compensation for brokering your loan.
3. The terms and conditions of your loan are fully set forth in the following Promissory Note and Arbitration Agreement you will sign when the loan is made. You should read that loan agreement and all other documents carefully. You are under no obligation to make the loan if you do not wish to sign the loan documents." "" \* MERGEFORMAT }


The Yellow highlights indicate the field that the "IF" statement needs to reference. The Magenta highlights are so I can easily find the formula.
I have four (4) of these "IF" statements, one right after the next, as we have 4 different "Affiliate Id's", and each one has a different paragraph that needs to be displayed in the document.


Any help on this would be greatly appreciated.



Thank you,
Shawn

Reply With Quote
  #2  
Old 05-10-2019, 03:24 PM
macropod's Avatar
macropod macropod is offline "IF" Staement(s) not Working properly Windows 7 64bit "IF" Staement(s) not Working properly Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

There is no highlighting in your post.

Presumably, OPPORTUNITY_AFFILIATE_ID is the name of a field in your data source. In that case, you need a field construction like:

{IF«OPPORTUNITY_AFFILIATE_ID»= 4418 "Payday Loan, LLC dba Payday Money Centers, with its headquarters at 300 S. Harbor Blvd., Suite 500, Anaheim, California 92805, California Finance Lender License No. 603 6712 (referred to herein as ‘‘Broker’’) is acting as a broker to assist LoanMe, Inc., 1900 S. State College Blvd., Suite 300, Anaheim, CA 92806, California Finance Lender License No. 603K061 (‘‘Lender’’) in making your loan. By signing below, you acknowledge that you understand:
1. Broker is acting as a loan broker to help Lender make your loan. This assistance to Lender may include obtaining information and documents necessary for Lender to make your loan. 2. Broker will receive $300 from the Lender as compensation for brokering your loan.
2. Broker will receive $750 from the Lender as compensation for brokering your loan.
3. The terms and conditions of your loan are fully set forth in the following Promissory Note and Arbitration Agreement you will sign when the loan is made. You should read that loan agreement and all other documents carefully. You are under no obligation to make the loan if you do not wish to sign the loan documents."}

Note 1: The field brace pairs (i.e. '{ }') for the above example are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac or, if you’re using a laptop, you might need to use Ctrl-Fn-F9); you can't simply type them or copy & paste them from this message. Nor is it practical to add them via any of the standard Word dialogues. Likewise, the chevrons (i.e. '« »') are part of the actual mergefields - which you can insert from the 'Insert Merge Field' dropdown (i.e. you can't type or copy & paste them from this message, either). The spaces represented in the field construction are all required.

Note 2: You cannot use double quotes as part of the output of an IF test. Hence the paired single quotes for ‘‘Broker’’ and ‘‘Lender’’.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 05-13-2019, 10:17 AM
shawn.low@cox.net shawn.low@cox.net is offline "IF" Staement(s) not Working properly Windows 10 "IF" Staement(s) not Working properly Office 2016
Novice
"IF" Staement(s) not Working properly
 
Join Date: May 2019
Location: Aliso Viejo, CA.
Posts: 24
shawn.low@cox.net is on a distinguished road
Default "IF" Staement(s) not Working properly

Hi Paul,


Thank you for the feedback.
This is where it gets so darn frustrating. I am using Conga Composer to "create" the Document and send it to our customers for signature (We use DocuSign for the signing part).
So here is the bigger picture, and we'll see if this changes things.
We use Salesforce.com as our CRM. Our Loan Agents take the loan app using SFDC, and then the goal is that they click a button which says "Send Loan Agreement".
When they click this button (the button is created by Conga Composer), there is an automated process that gets the Word Doc called "Consumer Loan Promissory Note & Disclosure" (which has already been upload into Conga Composer), and pulls in data from Salesforce through "Merge" fields (Conga formats the merge fields using double bracers, i.e. {{Affiliate_Id}}. If I only use single bracers, Conga will not merge the data from SFDC into the document.
In Addition,

I need the Word doc to perform some logic, hence the addition of the "IF" statement into the Word Doc itself. This is where I might be having some issues (at least I think so).

I am asking Word to reference a field in SFDC (In this case, its a field called "Affiliate Id". I don;t need the data merged into the document, just referenced).
Word needs to check that field, and IF the value is X, then add the paragraph that I have for the "True" part of the IF statement, and if the value is NOT X, then do not add anything.
So basically, the IF statement needs to check and see if the Affiliate Id equals X, then add, X paragraph, if it's Y, then add the Y paragraph, if it's A, then add the A paragraph, if it's B, then add the B paragraph, and if it none of those values, then only add the C paragraph.
I need to do this in about 4-5 places through out the Word Doc, adding certain paragraphs depending on a field value in SFDC.


I think where I am going wrong is that I need to use the double bracers - {{ }}, when it's a Merge Field and data needs to be merged into the document, BUT...
When I use an IF statement, I need to use the standard double brackets - << >> that Word uses.
Or do I need to use any bracers at all, since all I'm doing is checking the value of the field (no merge necessary) for a specific value, and if that value is X, then add the "True" text, and if it's not, leave the blank ("False" value would be "").


Am I on the right track, or am I way off?


Thanks Paul,
Shawn
Reply With Quote
  #4  
Old 05-13-2019, 03:13 PM
macropod's Avatar
macropod macropod is offline &quot;IF&quot; Staement(s) not Working properly Windows 7 64bit &quot;IF&quot; Staement(s) not Working properly Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by shawn.low@cox.net View Post
there is an automated process that gets the Word Doc called "Consumer Loan Promissory Note & Disclosure" (which has already been upload into Conga Composer), and pulls in data from Salesforce through "Merge" fields (Conga formats the merge fields using double bracers, i.e. {{Affiliate_Id}}. If I only use single bracers, Conga will not merge the data from SFDC into the document.
As indicated in my previous reply, you should be inserting a mergefield, which will look like «OPPORTUNITY_AFFILIATE_ID» or {MERGEFIELD OPPORTUNITY_AFFILIATE_ID}, not {Affiliate_Id}. It's also now unclear whether the field is named 'OPPORTUNITY_AFFILIATE_ID' or 'Affiliate_Id'...
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 05-13-2019, 03:33 PM
shawn.low@cox.net shawn.low@cox.net is offline &quot;IF&quot; Staement(s) not Working properly Windows 10 &quot;IF&quot; Staement(s) not Working properly Office 2016
Novice
&quot;IF&quot; Staement(s) not Working properly
 
Join Date: May 2019
Location: Aliso Viejo, CA.
Posts: 24
shawn.low@cox.net is on a distinguished road
Default

The field itself is called "Affiliate Id". The Object is the "Opportunity" Object in SFDC.
I wasn't sure about this either, if I needed to have the Object as well as the field name, or if just the field name would be correct.


I have also been told that the Merge Field needs to be in quotes (" "), so it would look like this: { IF "<<OPPORTUNITY_AFFILIATE_ID>>" = ...} Is this correct?
I understand that numbers do not need quotation marks, but I thought text fields did, but wasn't sure.


Thanks
Shawn
Reply With Quote
  #6  
Old 05-13-2019, 03:36 PM
macropod's Avatar
macropod macropod is offline &quot;IF&quot; Staement(s) not Working properly Windows 7 64bit &quot;IF&quot; Staement(s) not Working properly Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by shawn.low@cox.net View Post
I have also been told that the Merge Field needs to be in quotes (" "), so it would look like this: { IF "<<OPPORTUNITY_AFFILIATE_ID>>" = ...} Is this correct?
That is not correct. Look at the many examples in the Mailmerge Tips and Tricks 'Sticky' thread at the top of this forum: https://www.msofficeforums.com/mail-...ps-tricks.html
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 05-15-2019, 09:16 AM
shawn.low@cox.net shawn.low@cox.net is offline &quot;IF&quot; Staement(s) not Working properly Windows 10 &quot;IF&quot; Staement(s) not Working properly Office 2016
Novice
&quot;IF&quot; Staement(s) not Working properly
 
Join Date: May 2019
Location: Aliso Viejo, CA.
Posts: 24
shawn.low@cox.net is on a distinguished road
Default

Hi Paul,


I went through the thread you suggested (mailmerge Tips and Tricks) and I've made some adjustments to the IF statements and for the most part, things are working properly now.
I am still having one issue.
The "single" (for lack of a better way to explain it) action IF statement, things work great (i.e. {IF <<merge_field>> = 1 "1" "2"}.
Its when I need the IF statement to evaluate multiple things that I have a problem.
We do business in several States, and depending on which State the customer lives in, I need the correct paragraph to appear in the document.
I have tried "nesting" the IF statements like this:
{IF <<Borrowers_State>> = "CA" "CA Paragraph" " { IF <<Borrowers_State>> = "NV" "NV Paragraph" "" }} (I have added up to 9 nested IF statements in the past),
and I have tried splitting the IF statements up like this:
{IF <<Borrowers_State>> = "CA" "CA Paragraph" "" }
{IF <<Borrowers_State>> = "NV" "NV Paragraph" ""} (and so forth)


I was wondering if the issue might be that I am using a Merge Field in the IF statement? I don;t actually need the data to be "Merged" into the document, I just need the IF statement to check the Value that is in that field (Borrowers State) and based on the result, either include, or exclude the correct paragraph.

I hope that makes sense?



I will continue to look through the multiple string within this forum, but if you have some insight I would greatly appreciate the help.


Thank You Paul, and Cheers,
Shawn
Reply With Quote
  #8  
Old 05-15-2019, 04:16 PM
macropod's Avatar
macropod macropod is offline &quot;IF&quot; Staement(s) not Working properly Windows 7 64bit &quot;IF&quot; Staement(s) not Working properly Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

As clearly stated in Note1 of Post 2, you cannot type the << >> which, in any event, appear as « » when inserted via 'Insert Merge Field'.
A series of consecutive (not nested) IF tests coded along the lines of:
{IF«Borrowers_State»= "CA" "CA Paragraph"}{IF«Borrowers_State»= "NV" "NV Paragraph"}{IF«Borrowers_State»= "AZ" "AZ Paragraph"}
could handle every state in the US.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Tags
@dynamic document, @if statements

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Presentation will not print properly in "Notes" layout mnt23 PowerPoint 4 08-07-2018 06:53 AM
&quot;IF&quot; Staement(s) not Working properly Word 2014: WPerfect 6.x full-justification "stops working" dan_1 Word 12 01-24-2017 12:43 PM
&quot;IF&quot; Staement(s) not Working properly Is there a free download of a"working" PowerPoint 2003, not just a viewer. oldtrout PowerPoint 1 01-10-2016 01:23 AM
&quot;IF&quot; Staement(s) not Working properly 2007 Calendar - How do I show "working offsite" but available for phone meetings? Stan Outlook 2 08-16-2011 09:30 AM
&quot;IF&quot; Staement(s) not Working properly How to choose a "List" for certain "Heading" from "Modify" tool? Jamal NUMAN Word 2 07-03-2011 03:11 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:25 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft