Code to hide lines where the MergeField is Zero, using IF

[helens63]

Hi. 

I use software called WorkflowMax which uses MSWord format for custom Invoice Templates.  There is a type of Invoice which shows several columns of data for each item on the invoice inlcuded Original Quoted Price, what was Previously Claimed, what's being claimed on the current invoice, and what is remaining as outstanding.

I have managed to work out the code to not show a "0.00" for any particular field when the value of the field is Zero so that part is all good.   As below in the third line where there is no "Claimed Incl This Invoice" for the Task "Consulting RMA Ecology", nothing prints.

However, I need conditional code so that I can not show all 4 columns of a specific line, if just one of those columns is Zero.  ie. if Nothing is being claimed on part of the quote in an invoice, then I don't want to print the other columns for that line, which do still have values (Task Name / Quoted  / Previously Claimed/ Claimed Incl This Invoice / Balance Remaining).   So as an example, to not show the Quoted column it will need to look and see if there is a zero value in the "This Invoice" column.   I've really had a go with it but just can't make it work!  

[#2]

@helens63 Are you saying that you want to conditionally hide some of the columns, INCLUDING the header of the column.  If that is the case, you will need to conditionally display variations of the table with each variation only containing the wanted columns.

[#3]

@Doug_Robbins_Word_MVP    Hi Doug,  thank you for entering this conversation.  My understanding of the terminology might be incorrect.  When you say the header row I'm thinking that means the titles, or the first row of the table.  I don't want to hide that.  It will be fine to display the header row, even if some of the rows underneath are empty.    But for any particular row, if the Current Claim is 0, then we don't want to display anything on that row.  I've found it easy enough to stop the Current Claim part not showing, but not the other columns for that row.   The owner is thinking that if there is no Current Claim for part of the Quote, then he doesn't want to show the original Quoted price, what's been claimed before and what's still outstanding for that particular part of the quote, on that invoice. 

[#4]

@helens63 Sorry, I have not been able to determine exactly what the criteria is, which might be me, or it might be because you have used a term "Current Claim", rather than the relevant MergeField name.  Can you re-phrase the requirement, making use of the actual MergeField names.

I am not sure how WorkflowMax processes the data, but the Many to One facility of my MergeTools Add-in, which can be used from creating output for items such as a multi-line invoice, the test for a field that is empty must be for " " rather than "".

[#5]

@Doug_Robbins_Word_MVP    Hi Doug. I'm sorry I'm not much help myself. I've done another snip with the merge field codes revealed but I'm unsure if that's what you mean. The Merge Field that is showing as highlighted in grey is the problem one that doesn't work. I'm trying to say in that field to look at the field under "This Invoice" heading and if that one is Zero, then not to print.    

[#6]

@helens63 In the Quoted Column, use:

[#7]

Thanks Doug. Seems I was on the right track but completely wrong syntax! I'll let you know how I get on. Thank you so much - I really do appreciate you taking the time.

[#8]

Well, I'm not getting the error message any more, but it's still not liking it - I'm just getting the word "MERGEFIELD" now when updating - I have triple checked my syntax... I'm wondering if we should take this to a screen share session (I expect chargeable). ?

[#9]

@helens63 Can you show a screen shot of the mail merge main document with the field codes displayed, or better, upload a copy of that document and the data source.

[#10]

@Doug_Robbins_Word_MVP 

Thanks Doug.  I've attached the template and also made a quick video of within the software so you can see more - I hope that helps. Here's the link to the video 

[#11]

@helens63 You must either use the Insert MergeField facility or CTRL+F9 to insert the field delimiters and type the fieldname inside them.  You cannot use the { and } characters that can be inserted using those keys on the keyboard.

[#12]

@Doug_Robbins_Word_MVP   Thanks Doug. I've always done it that same way, but I found the field code and checked - perhaps I have not understood that part? 

[#13]

@helens63 I have uploaded a document in which I have corrected the field coding in the first table.

[#14]

Thanks Doug. Unfortunately it's still not working. I feel like I'm just going to leave it for the moment - it may be that it's just not technically possible. Luckily there is a work around where the invoice can be exported to MSWord and any blank lines deleted before resaving as a PDF - not ideal but at least it's an option.

[#15]

@helens63 If you can export the data to Excel, you could use the Many to One facility on my Merge Tools Add-in that is contained in the MERGE TOOLS ADD-IN.zip file that you can download from:

https://mergetoolsaddin.com/

Extract the files from the archive and read the:

“READ ME – Setting up and using the Merge Tools Add-in.pdf

to see how to install and use the various tools.

Using those tools, it is possible to perform the following types of merge that cannot be done with Mail Merge “out-of-the-box”:

• Merge to e-mail messages either with or without attachments, with the documents created by the merge being sent as either Word or PDF attachments or as the body of the e-mail message .  The email messages can, if necessary, also be sent to CC and BCC addresses and the subject of the message can include data from a field in the data source.
• Merge to individual documents in either Word or PDF format with the filenames being supplied by the data in one of the fields in the data source
• Many to One type merges, which can be used for creating documents such as invoices where there are multiple records in the data source that have common data in one of the fields
• Merging to a document that will include a chart that is unique to each record in the data source
• Merging a document with Content Controls
• Merging a document that contains Legacy FormFields
• Duplex Merges
• Merging to a printer that will collate and staple the output created from each record in the data source.

The requirements for using the system are:

• The mail merge main document must be of the Letters type, though that does not mean that the output cannot be sent as an e-mail message where relevant.
• For the Many To One, Merge with Attachments and Merge to Individual Docs utilities, the data source may be either a table or query in an Access database, or in the form of an Excel worksheet. For the Chart Merge utility, see the Mail Merging with Charts document that is included in the Merge Tools Add-in Zip file for additional requirements for the data source for use with that utility
• For a data source in the form of an Excel worksheet, the field names must be in the first row of the worksheet and there must be a field name in all of the cells in that row that are within the range of columns that contain the data.
• For both types of data source, the field names must contain only alphanumeric characters (No @,#,$,%,&,(,), etc) and the field names must not start with a numeric character (0-9). The number of characters in the field names, including spaces, must not be more than 40.

The MergeTools Add-in will NOT work with the “New Outlook”, which is just a re-badged version of Windows Mail and like its predecessor, it does not support automation and hence will not send the messages created by the MergeTools Add-in.

Be aware, that any messages that you had tried to send with the “New Outlook” will have been placed in the Outbox of the original Outlook and they will be sent as soon as you revert to that version of Outlook.   If you do not want that to happen, you should put Outlook Off-line, or disconnect your computer from the Internet.