Need help skipping blank values in text box using Fields

Started by djc664, February 16, 2017, 02:59:50 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

djc664

I'm working on some custom shapes which are meant to be multi-purpose. They have a set number of fields, but not all of them are in use every time. Additionally, I need each of the fields to be formatted in a particular way, which keeps me from simply joining all of the text together in one long wrap. Eventually these will be used with Link Data with Shapes functionality.

Example:
Title (required; formatted large, bold)
Data1 (usually entered; normal text)
Data2 (usually not entered; normal text)
Note (sometimes; formatted small italic in red)

I'm trying to get the output as this if they are all filled:
Title
Data1
Data2
Note

But if Data 2 is blank, it should look like this:
Title
Data1
Note

The keys here are that I need the text to be as condensed as is possible, and to skip over values that are blank. Each line needs to be able to have different text formatting in whatever combination.

I've run into something that may be a bug, or just something unique to my installation (Visio Pro 2013).

My solution was based on an old Mail Merge trick I used back in the day from Word that basically enclosed the "optional" carriage returns in the field code.

Example Code. Each curly bracket represents a field, and the formula I entered in each. Each individual value can be text formatted, which allows the different looks to be achieved.
{=IF(FORMULAEXISTS(Prop.Title),Prop.Title&CHAR(10),"")}{=IF(FORMULAEXISTS(Prop.Data1),Prop.Data1&CHAR(10),"")}{=IF(FORMULAEXISTS(Prop.Data2),Prop.Data2&CHAR(10),"")}{=IF(FORMULAEXISTS(Prop.Note),Prop.Note,"")}

What I anticipated happening was that all the lines above would show the value and add a carriage return IF the field was not blank. What I found is that the CHAR(10) appears to remain no matter what for each field; the value itself disappears and reappears, but the carriage return never seems to go away or get refreshed.

While I would appreciate others confirming that this happens to them as well, I'd love any help that can be offered in making this work if possible.

wapperdude

#1
The char(10) remains because it is hard coded into each test.  In your approach, you need to test if there are any entries that follow the current entry, if so, add a char10, if not, no char10.

Wapperdude
Visio 2019 Pro

djc664

Quote from: wapperdude on February 16, 2017, 03:07:51 PM
In your approach, you need to test if there are any entries that follow the current entry, if so, add a char10, if not, no char10.

Wapperdude
Could you please point me in the direction of how I would test for entries that follow, especially if the next field may or may not be blank?

wapperdude

1st, what version/release of Visio are you using?  As I only have V2007 Std, there may be options that might facilitate the solution in the newer releases.

But, you have the basic idea .... check to see if a property has entry.  You need to extend that approach for the char(10)'s.  So, let's say you have 1st entry only.  You then check each remaining entry to see if any exist.  If so, include a chr10, and next entry. If not, do not add chr10.  You need to work down thru the entries, test each subsequent entry.

There are various ways to accomplish this:   one really long, concatenated formula, or make separate tests, store results, then "poll" the results and build a simple formula that grows/shrinks based upon which entries present.

Newer versions which use data recordsets and data graphics might have options to make this less painful, but I cannot advise on such things.

Wapperdude
Visio 2019 Pro

Yacine

Here is a formula that definitely works and the char10 is not shown if the field is empty.
Prop.Nr2&IF(STRSAME(Prop.bmk,""),"",CHAR(10)&Prop.bmk)&IF(STRSAME(Prop.pneumatikausgang,""),"",CHAR(10)&Prop.pneumatikeinheit & "-A"& Prop.pneumatikausgang)
Yacine

djc664

Quote from: Yacine on February 16, 2017, 04:03:04 PM
Here is a formula that definitely works and the char10 is not shown if the field is empty.
Prop.Nr2&IF(STRSAME(Prop.bmk,""),"",CHAR(10)&Prop.bmk)&IF(STRSAME(Prop.pneumatikausgang,""),"",CHAR(10)&Prop.pneumatikeinheit & "-A"& Prop.pneumatikausgang)
Thank you for this! While this code definitely skips blanks, but it does not allow different text formatting for each variable. Each of the properties that I need to display may have very different formatting needs.

In the end, the path wapperdude put be on ended up being a working model. Here's what I ended up with, to continue the example above:

{=IF(STRSAME(Prop.Title,""),"",Prop.Title)}{=IF(OR(STRSAME(Prop.Title,""),STRSAME(Prop.Data1,"")),"",CHAR(10))}{=IF(STRSAME(Prop.Data1,""),"",Prop.Data1)}{=IF(OR(STRSAME(Prop.Data1,""),STRSAME(Prop.Data2,"")),"",CHAR(10))}{=IF(STRSAME(Prop.Data2,""),"",Prop.Data2)}{=IF(OR(STRSAME(Prop.Data2,""),STRSAME(Prop.Note,"")),"",CHAR(10))}{=IF(STRSAME(Prop.Note,""),"",Prop.Note)}

Each of the fields, marked by {}, can then be formatted as appropriate for the output text, if any.

I recommend adding "TRUE" and "FALSE" to the IF statement outputs for each of the property fields on the first round, so that you can easily select them and format the text as appropriate. You then have to go back in and replace those with the formula above, of course, but it's far easier than trying to find a blank value out of a few in a row. :)

Thank you all for your assistance!!

wapperdude

As far as inserting the text, removing blank entries, this could all be done within shapesheet, using insert>Field.  However, there are a couple of issues that are pertinent to your application.  One, the "long" formula works fine with a single field insert.  But, you sacrifice individual formatting.  Alternatively, you can insert separate fields, hide the field as needed, thus retain individual formatting.  However, you give up hiding the blank entries.

If you do it programmatically, then, you ought to be able to retain the formatting.

Wapperdude
Visio 2019 Pro

wapperdude

After thinking about it, trying to do all that you've indicated is more easily accomplished with separate shape for each field. This allows highly flexible formatting, relatively easy ignoring empty parameters.  The attached file has 3 examples:

On the left, the single, formula approach.  Covers all cases of for blank entries.
In the middle, a grouped shape with shapes for each entry.  Blank entries are shrunk to zero.  The trick is setting the PinY of each shape to account for blank entries.
The shape on the right is a modified version of the middle shape which allows the font size to reduce according to amount of text.  Each shape scales independently of the others, but, that could be changed.  The maximum text size is the group font size.

All 3 shapes bring up the data entry by double clicking.

Enjoy!
Wapperdude
Visio 2019 Pro

djc664

That's an interesting approach, thank you! That won't work for this particular case I'm working on, but I'm sure I'll find a way to leverage that example eventually.

wapperdude

Other than needing more subshapes for additional fields, this ought to meet your needs.  It does everything you indicated in the original post.

Wapperdude
Visio 2019 Pro

wapperdude

#10
Hmmmm.   Inspired by another post, this should be more satisfactory. 

Uses field inserts for each defined property that needs to be displayed.  In the User-defined section, there is a row for each data property that contains a formula governing the display behavior.  The Insert Field actually calls these user.rows, rather than the data property directly.

Each field entry is a separate entry, not a long catenation.  That is, before each subsequent entry is made, a manual "enter" is needed to go to the next line.  This allows each line to be individually formatted, and avoids use of multiple shapes.  Each formula is unique depending upon where it is placed in the stack.

Wapperdude
Visio 2019 Pro

djc664

@wrapperdude: The scenario I put forth was very specific for clarity here, but was not 100% of the requirements of the overall purpose (which is much more complicated and confusing to go into). Just wanted to be clear that I am resolved, thanks to your and Yacine's help, and I won't be commenting on the other solutions you're cooking up. ;)