Dynamics GP Word Template If-Then Statements

Hello:

I'm a Microsoft Dynamics GP consultant who is trying to incorporate If-Then logic in the Word template functionality, to have a logo print on the Check Remittance based on the Checkbook ID of the checks cut.

The "CheckbookID" field serves as a bookmark.  So, the following is my syntax:

IF { REF CheckbookID }  = "ACBRTCDISC     " { IncludePicture “L:\\GP Templates\\RTC Logo_090513PM.png” } { IncludePicture “L:\\GP Templates\\PCI Logo_090513PM.png” }

I have spaces at the end of the checkbook ID, because I saw in SQL that there are five blank spaces at the end.  

This If-Then field produces the phrase "REF" on my Word document.  When I generate the Word template out of GP, it only says "REF".  It does not give me any data.  How can I get this fixed?

Thanks!  Much appreciated!

TBSupport
LVL 1
TBSupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DrTribosCommented:
Hi there,

You are asking if the text in bookmark CheckbookID is exactly equal to "ABCRTCDISC     ".  So, for example if you have 5 spaces but the bookmark has 0 spaces it will evaluate to false.

Therefore, to get a 'true' your IF expression must be exactly what the bookmark says it is...

You can find out what the bookmark is as follows:
Add a field by pressing Ctrl + F9
Add text to that field:   Ref CheckbookID
It should look like { Ref CheckbookID }
Turn field shading on show hide (paragraph character on the home tab) & field shading (in word options)
Toggle field codes so you see the text, it should be shaded and any dots in the shaded area are spaces that have been included in the bookmark.

Now... change the bookmark and update the fields again
(click on your picture and press F9 or use print preview)

If you were doing this manually then to obtain better control over the bookmark you can use an ASK field...  { Ask CheckbookID "Pls enter a value" }

However, I think you are exporting data to Word therefore your actual problem might be putting the checkbookID into the target bookmark?  

Can you upload an example of an exported document?

Hope this helps!

PS. Syntax requires { } around the entire statement...
PSS. Word frustrates me too.
0
TBSupportAuthor Commented:
Hi Dr. Tribos!

I apologize for the second posting.  Since my work is specialized and since I'm not being successful at this, I'm getting desperate.

Attached is my revised Word template.  You can see in the upper right, in a small square, the word "REF".  That's my If-Then statement.

Yes, you're right.  My app is exporting data to Word.

My concern isn't so much those fivespaces, but I'm glad you told me that I don't need them.  

But, even without the spaces, the problem remains.  No matter what I do, I cannot get this to work.  

I have, also, attached an example of the export.  Really, it's just the top portion of a check.  I couldn't let others in cyberspace see the sensitive financial information.  That's why I didn't show all of it, in my attachment.

Nevertheless, it makes its point.  The If-Then statement in the upper right shows the word "REF", instead of a logo.

Thanks!  You're excellent!

TBSupport
Check-Remittance-Template-LogoCh.docx
Example.docx
0
DrTribosCommented:
Hi there,

After some searching I realised that the {} were not added using Ctrl + F9.  You must use Ctrl + F9 otherwise they are just "curly brackets" and not a field.

I deleted the offending brackets and selected the relevant text in the field then pressed Ctrl + F9 to enclose in a field.

The other thing I did was to change the text in the bookmark to match the if statement.

Pls see attached file.

Also the picture below shows the very subtle difference that you might be able to see between curly brackets and a field.  The field has a kind of pixellated border... Must press Ctrl + F9 (or use insert field) to achieve this!!
Curly brackets versus fieldField CurlyBracket Field CurlyBracket...  also you can see my cursor near the middle.
Midnight here, hope this helps
Check-Remittance-Template-LogoCh.docx
0
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

TBSupportAuthor Commented:
Hi Dr. Tribos!

Again, I thank you for the help.  But, I'm still not able to get this to work.   Let me tell you how I'm doing this.

In the "Insert" tab of Word, there is a "Quick Parts" button where you can choose "Field".

On the left side of the window that appears, I choose "IF".  In the upper right, I paste the following:

IF REF CheckbookID = "ACBRTCDISC" IncludePicture “L:\\GP Templates\\RTC Logo_090513PM.png"  IncludePicture “L:\\GP Templates\\PCI Logo_090513PM.png"

You're right.  At first, I was putting brackets around these fields.  So, I decided to instead paste the above text in the window and let Word put the brackets in.  

Well, that at least got the field to name itself "CheckbookID" instead of "REF".

If you look in the upper left-hand corner of the attached document, right-click on "CheckbookID", and choose "Edit Field", you'll see what I mean.

Anyway, when generating the Word document, the field continues to only show the phrase "CheckbookID", rather than any sort of logo.

Also, my actual bookmark in the upper right-corner is a Dynamics GP field for "Checkbook ID".  It looks like X's, but it's not.  So, my bookmark does not require changing.  :)

I can't tell what you did on your attachment, because it comes across as a "red X" and Word will not let me "play with it".

In your first posting, you gave some instructions.  Could you please state what is meant by the following?:

Turn field shading on show hide (paragraph character on the home tab) & field shading (in word options)
Toggle field codes so you see the text, it should be shaded and any dots in the shaded area are spaces that have been included in the bookmark.
Now... change the bookmark and update the fields again
(click on your picture and press F9 or use print preview)


TBSupport
Check-Remittance-Template-LogoCh.docx
0
DrTribosCommented:
Hi there,

So the basic problem is that each field must be added individually.  I will show you this in images below. First, to answer the question of what I meant which you italicised.

These are word settings that I suggest you use... (I'm going to add to it just a bit).


1. Field Shading On & Show Bookmarks:

How: File > Options > Advanced - scroll to "Show Document Content" (about 1/2 way)
Word settings
Note that show bookmarks is ticked & show field shading is set to always


2. Turn "Show Hide" ON:

How: Main Ribbon - press the show hide button
Show Hide ButtonNote that the show hide button is the last button in the Paragraph section (just above the '5' on the ruler), it looks like a backward P with 2 legs...  The show hide button will show dots to represent spaces and 2-legged-backward-P to represent end of paragraph.  If you have tables, it will show a round character to represent the end of cell and end of table.


3. Toggle Field Codes

How: You can do this using Word Options in 1 above (show field codes instead of values), but it is quicker to just press Ctrl + A (select all) then press Shift + F9 (toggle codes).  
Or if you only want to toggle a single field you can select just the 1 field.  Note that Ctrl + A will not select items in the Header unless you are in the header.  So you need to first open the header.  


4. Update Fields

How: With your field selected press F9 - the field should update.
Note: Using F9 to update fields is generally quick but will only work on selected fields.
Another way to update a field is to right click on it, but sometimes the right click context menu fails to appear if the field contains an image or spelling error.
0
TBSupportAuthor Commented:
Hi Dr. Tribos!

Thanks, for this information.

Any thoughts as to why I can't get this to work, in general?  Should I not be using that Insert...Quick Parts...Add Field option?  Is that my problem?

I understand about placing brackets around the three fields.  But, when I generate from GP to Word, I do not get a logo.

What's the best way to do this?  I just feel like I'm not going to get this.

TBSupport
0
DrTribosCommented:
How I built the field...

Add a field by pressing Ctrl + F9, now I have:
{  }

Add text to the field, now I have:
{ IF }

Add a second field to my field by pressing Ctrl + F9, now I have:
{ IF { }}

Add text to my second field, now I have:
{ IF { REF CheckbookID }}

Add the condition (just text) to my If field, now I have:
{ IF { REF CheckbookID } = "ACBRTCDISC" }

Add the True and False to the If field, 2 more fields added using Ctrl + F9, now I have:
{ IF { REF CheckbookID } = "ACBRTCDISC" { }{ }}

Finally I add the true text for the true and false text for the false..., now I have:
{ IF { REF CheckbookID } = "ACBRTCDISC" { IncludePicture “L:\\GP Templates\\RTC Logo_090513PM.png” } { “L:\\GP Templates\\PCI Logo_090513PM.png" }}
0
DrTribosCommented:
Ok try replacing the logo file paths with the words "True" and "False" like this:
{ IF { REF CheckbookID } = "ACBRTCDISC" "True" "False"}

Then at least we can see if the If part of the field is working.
0
DrTribosCommented:
Hi what do you mean by this:
But, when I generate from GP to Word, I do not get a logo.

What exactly are you generating in GP, the bookmark text or the whole of the field codes, or something else?

Also, please try this in an otherwise blank document away from GP so we can tackle 1 thing at a time.

BTW - image holder with a cross through it indicates the image can not be found, might be an issue with file name or path.

Thanks
0
TBSupportAuthor Commented:
Even with "True" or "False", it still says "CheckbookID" instead of displaying a logo.

Shall I dump using that Insert...Quick Parts...Field option?

TBSupport
0
DrTribosCommented:
I think it would be easier to NOT use QuickParts to insert fields, however I also think it is easier to remember all the keyboard shortcuts instead of using the ribbon etc...  For the sake of this, try without using the QuickParts ;-)

Also, toggle the field codes and then paste a screen shot so I can see exactly what you have there, or send the file.
0
TBSupportAuthor Commented:
0
DrTribosCommented:
Um... you have not turned on field code shading... I can not tell if that is a field.  Can you just send the document?
0
TBSupportAuthor Commented:
Hi:

I had pressed "Submit" on accident.  I was in the middle of typing something.  Stand by.  :)

TBSupport
0
TBSupportAuthor Commented:
Hi There, Dr. Tribos:

So near, yet so far.  Attached is a revised set of screenshots.  The first is what I accidentally sent to you a few moments ago.

The second screenshot actually does show the correct logo, after I highlighted the statement that you taught me to type with Ctrl + F9.  I was elated that it appeared to work.

But, if you look at the third screenshot, you will see that I'm back to square one.  This is when I generate it from GP.

Unfortunately, I have to generate it from GP.  It's a package deal.  GP has Word template technology that allows end users to generate documents like Check Remittances straight to Word instead of to a standard GP report.  This If-Then statement formatting is something that I'm not so sure has ever been tried with this technology.

Where do you see an "image holder with a cross through it indicates the image can not be found, might be an issue with file name or path"?

TBSupport
IF.docx
0
DrTribosCommented:
Hi there,

In the 2nd screen shot the ACBRTDISC shows where the image was and also where the xxxxxxxxxx was.  But it looks like the bookmark where the xxxxxxxx was has been wiped.

I can see a bookmark in the table below.

Did you turn field shading on?  I can not see evidence of any field codes behind the ACBRTDISC in spot where the image was?  


Re the cross through the picture holder, I just mentioned it for sake of trouble shooting.  If, for example, you were to see a grey box with a cross through it, that would would mean you might have a problem with the path...
0
DrTribosCommented:
How do you tell GP where to put the information in the word document?  

Are you using an existing word template (note templates have dotx or dotm file extensions, documents have docx or docm file extensions)?
0
TBSupportAuthor Commented:
No, it hasn't been wiped.  The X's are a placeholder for the Checkbook ID field in GP.  That field/bookmark is on the right, and the place where a logo is supposed to show up is on the left.  But, for some stupid reason, it is displaying the name of the bookmark rather than a logo.

Yes, I turned field shading on, just before I created the second and third screenshots.

Hmm.....I guess this If-Then statement stuff doesn't work with the GP technology.  My guess is that it has to do with timing.  Perhaps, the interface between GP and Word needs a time delay where it can evaluate the bookmark or something along those lines.

Great!  I don't know what to tell the client.

If you can think of anything, I'm all ears.  But, I've been consulting on this app for years and I'm coming to the conclusion that the GP to Word interface just isn't strong enough to support If-Then.

TBSupport
0
TBSupportAuthor Commented:
I saw your question just now about where do I tell....  OK.  The Word document is a "knock off" of the GP standard report and pulls in the fields from that report.  I don't know how else to explain it.

TBSupport
0
DrTribosCommented:
Ok... well perhaps close the 1st question which was more focussed on word and (I think answered), that will also allow the focus to stay here.  

If field shading is ON then the ACBRTDISC in the top right is not a field?  Can you toggle field codes in that part of the document just to be sure.  

If GP is wiping the field then another approach is required.  We can look at using VBA (aka macros) to reinsert the fields... The macro would have to run on every document...

You could create a bookmark where the Logo belongs and a Macro could do the rest. Interested?
0
TBSupportAuthor Commented:
Very interested!  :)

The top right is a field from GP.

The top left is the If-Then statement where I can't get the logo to appear due to the limitation of GP Word technology.

TBSupport
0
DrTribosCommented:
In Word you will need to press Alt + F11 - this will show the VBE editor.  From there we will add some code.  

How the code is added will depend on how GP works with word.... I really don't know.  

How many reports are you creating at a time?
Where do the reports go? To the hard drive? Then where?


I did find a link to an MSDN article which talks about modifying templates for Dynamics... I hope the link is acceptable for EE standards:  Modifying MS Dynamics GP Templates

I suggest reviewing that first just to ensure the template is being used in the right way.  

By the way, I just noticed that the template you attached in comment a39494838 is a document and not a template.  There is a world of difference between them and I think the field codes etc. should be in a template that is stored in the GP database as described in the MSDN article.
0
TBSupportAuthor Commented:
Hi Dr.:

It is a template that is stored in the SQL database.  I'm not deviating from the norm, in any way.

But, a GP window is used to change the template into printed or even e-mailed Word documents.  In the case of the Check Remittance, a handful or even dozens of Word documents will be generated for each vendor whose being cut checks for.

There's no telling how many at a time.  It depends on how many vendors are in the check batch that the remittance is being processed for.

I'd still love to hear the ideas on the VBA, if it's possible.  :)

TBSupport
0
DrTribosCommented:
Ok, assuming that once the report is generated you don't want to change the logo, the base code is here:
Option Explicit

Sub addFields()
Dim oFld As Field
Set oFld = Selection.Range.Fields(1)

If ActiveDocument.Bookmarks.Exists("CheckbookID") Then
    Select Case ActiveDocument.Bookmarks("CheckbookID").Range.Text
    
        Case "ACBRTCDISC"
        oFld.Code.Text = " INCLUDEPICTURE " & Chr(34) & "L:\\GP Templates\\RTC Logo_090513PM.png"
        Case Else
        oFld.Code.Text = " INCLUDEPICTURE " & Chr(34) & "L:\\GP Templates\\PCI Logo_090513PM.png"
    End Select
    oFld.Update
End If

End Sub

Open in new window


you can replace "Case Else" with:
 Case "XXXXXXXXX"
where xxxxxxxxx would be the alternative text in your bookmark.

A possible disadvantage of this approach is that you now have a macro and may need to adjust security settings.  Due to EE file type restrictions I renamed the file extension to .zip.    To open it you will need to rename it to .docm if you want to open it.

Press clickme (Macrobutton Field) to launch the macro.  Your logo should appear.
Check-Remittance-Template-LogoCh.zip
0
DrTribosCommented:
I don't think VBA will be the answer you are looking for.  You would either need to add it to the template OR run it on each document AFTER it has been generated.

If you are able to run it AFTER generation then I can certainly help with VBA but it would require the PC to have a word template with macros and every user would require that template to get the functionality.

One thing that I am almost certain of is that the fields MUST be added to the template.  

There is an article here which (I think) explains how to modify a template in Word to then be used in Dynamics GP.  It is a fairly involved process, but I think you have all the required tools available.

Another issue, which just occured to me, if you do get the If Field working then you would need to convert to PDF (or something similar) to send to recipients?  This is because the fields would not work on other computers if the images were unavailable in the specified location, would be OK if everyone has access to the L drive....  

I sincerely think the article explaining how to create a template will be your best bet.

If it turns out the link to the article is prohibited you could search for:
"Creating a Word Template requires working with Microsoft Word and Dynamics GP Report Writer."

Question:
I'm a Microsoft Dynamics GP consultant who is trying to incorporate If-Then logic in the Word template functionality
Answer in this article?

Kind regards,
0
DrTribosCommented:
Any luck with the steps outlined in the article about template modification?
0
TBSupportAuthor Commented:
Hi Dr. Tribos!

Some.  I see the shading that you mentioned.  And, if I toggle prior to processing the template, I do see one of the logos.

But, the last issue is the most important and that's getting the Word template to recognize the Checkbook ID field as, well, a Checkbook ID so that a logo will appear when the Word document Is spat out of GP.  I don't have a way to tell Word that it is essentially a SQL database field.

When GP processes the template and spits out the resulting Word document, the field becomes basically a constant.  So, the If-Then statement loses references to the Checkbook ID and simply displays as the Checkbook name (ID), rather than as a logo.

I'm still working on it.  Fortunately, the client says that the issue is not as high on the proverbial "totem pole" as he thought, as compared to other tasks that I need to work on.  :)

TBSupport
0
TBSupportAuthor Commented:
Hi Dr. Tribos:

I conferred with engineers at Microsoft Dynamics, and they advised me that this cannot be done with Dynamics GP.  So, I'm out of luck.

Thanks for your help, just the same.  It's appreciated!  :)

TBSupport
0
DrTribosCommented:
Sorry to hear that.   Let me know if you would like to proceed with the VBA approach,  although I do not think it can meet your needs as it would need to be run on each document after it has been generated.
Kind regards,
0
TBSupportAuthor Commented:
Actually, over the weekend, I created a solution of my own using Dynamics GP's functionality, as follows:

(1)  Modified a copy of the check remittance template to contain a hard-coded logo and made that remittance template the default in the TEST company while making sure that the second check remittance template already contained a hard-coded logo;
(2)  Ensured that the Image Assignment window for Template Configuration Manager did not contain a default logo for the TEST company; and
(3)  Used the Report Template Maintenance window’s “Assign” button to assign the second check remittance in the TEST company to only those Vendor Classes that begin with “RTC”.  Such Vendor Classes are coded with a Checkbook ID.  

Upon taking these steps, the issue was resolved.  Since the RTC checkbook and check remittance is "pointed to" a hard-coded logo, this produced one logo for one check remittance and another for the other check remittance.  This is exactly what I was after.

Thanks, Dr. Tribos, again for your excellent help!

TBSupport
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TBSupportAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for TBSupport's comment #a39515233

for the following reason:

This solution uses the app's own functionality to eliminate the issue, and my client approved the solution.
0
DrTribosCommented:
Hi - I'm loath to object since I am not certain that the path taken would lead to a solution.  But I feel the question asked for a specific solution based on If Fields and the asker had several issues in their approach which were overcome.  The additional information in the article on template modification within GP Dynamics may well have provided the basis for the final solution (in conjunction with the information provided) but seems untested, it is however what was requested.  The asker has found an alternative answer that does not rely on If Fields and therefore abandoned the orignal pursuit resulting in a zero point allocation for this specific question.

I will be happy with 0 points if it is deemed appropriate.

Kind regards,
0
TBSupportAuthor Commented:
Hello:

I didn't mean to "take anything away" from the help that Dr. Tribos gave.  If it is best to "award points" to Dr. Tribos, then please do.

I was mainly just trying to update the case with the solution that I discovered using Dynamics GP's functionality, in case future GP users run into the same issue and want the solution.

I apologize to Dr. Tribos and to anyone else that I may have offended.

TBSupport
0
DrTribosCommented:
Hi there - I'm new at this so not really sure how it 'should' work, no offence taken :-)  I think this is where the lower grading becomes appropriate, but not sure.  Cheers,
0
DrTribosCommented:
In that case the method of closing the question seems fair.  Thanks for clarification.
0
TBSupportAuthor Commented:
I acknowledged Dr. Tribos's solution and mine.  My solution was geared toward finding the solution within Dynamics GP, and it was the solution accepted by the client.

Please award points to Dr. Tribos, as well, since his insights led me down the correct path.

TBSupport
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.