Author Topic: Concatenating Text from Multiple Fields into a Single Data Graphic Callout  (Read 8280 times)

0 Members and 1 Guest are viewing this topic.

Lizzy

  • Newbie
  • *
  • Posts: 6
Hi All,

Is there a way to create a concatenated text string to be placed into a single Data Graphic callout from multiple cells in a linked data source?

For example: I have 10 records with 6 fields. When the data from two fields (columns) match between two or more records (rows), I would like the data from a third field in each of the matching records to be concatenated in a single string for display in a single Data Graphics callout (separated by commas).

Any help would be greatly appreciated!!!

Lizzy  :)

Visio Guy

  • Administrator
  • Hero Member
  • *****
  • Posts: 1735
  • Smart Graphics for Visual People...n' Stuff
    • Visio Guy
Re: Concatenating Text from Multiple Fields into a Single Data Graphic Callout
« Reply #1 on: September 20, 2009, 06:09:34 AM »
Hi Lizzy,

When you are setting up data graphics, there is a "Custom Field" or "Custom Formula" expression where you can do this, if you know the ShapeSheet cell names.

For example:

    = Prop.Val1 & ", " & Prop.Val2 & ", " & Prop.Val3

would display:

    value1, value2, value3

Maybe that will get you started?
For articles, tips and free content, see the Visio Guy Website at http://www.visguy.com
Get my Visio Book! Using Microsoft Visio 2010

Lizzy

  • Newbie
  • *
  • Posts: 6
Re: Concatenating Text from Multiple Fields into a Single Data Graphic Callout
« Reply #2 on: September 20, 2009, 07:39:11 PM »
Hi Visio Guy,

Thanks for the quick response. I am still missing a few pieces of the puzzle. Actually, I don't think I described the problem correctly.

I have two recordsets. The first recordset has a value that I want to match to several rows in another recordset. Then I need the data from a different cell from all of the matching rows in the second recordset to display in one shape separated by commas.

Here is an example of the data:

Recordset 1:

Process           Nomenclature                                                                  Function
4A                 Shift Briefing: Review Work Requests & Assignments         7
4B                 Retrieve Tech Docs                                                    7
4C                 Perform Maintenance                                                  7
4D                 Inspect / Function Check of Mx Performed                      7


Recordset 2:

Process    Function               Skills            Sequence
4A            7                      M-56            1
4A            7                      M-56A          2
4A            7                      M-59            3
4A            7                      M-59A          4
4A            7                      M-64            5
4B            7                      M-56            1
4B            7                      M-56A          2
4B            7                      M-59            3

When the Process in Recordset 1 equals 4A, then I want to identify all of the rows in Recordset 2 that have the Process 4A, grab the data from the Skills cell and display them in a single shape (ideally using the sequence order identified in the Sequence column). Using the above data my shape would contain the concatenated text string “M-56, M-56A, M-59, M-59A, M-64”.

I am worried that we may have to do a query in Access (the data source) to send over the data already formatted, which will eliminate the ability to edit the data in Visio and have it update the database (very desirable in this case). Can you see any other to solve this problem?

I am very grateful for your help and time!

Lizzy
« Last Edit: September 20, 2009, 11:21:45 PM by Lizzy »

aledlund

  • Hero Member
  • *****
  • Posts: 1412
Re: Concatenating Text from Multiple Fields into a Single Data Graphic Callout
« Reply #3 on: September 20, 2009, 09:31:25 PM »
If I understand what you're trying to do, the choice comes down to writing the summarization code in Access and delivering it as a view set to Visio or writing code in Visio to create the field data that you want  to put into the datagraphic.
Given your desire to have the user interact with the data in visio and roundtrip it back to the database(?), you might consider writing the code in vba in visio.
al

Lizzy

  • Newbie
  • *
  • Posts: 6
Re: Concatenating Text from Multiple Fields into a Single Data Graphic Callout
« Reply #4 on: September 20, 2009, 11:19:55 PM »
Hi Al,

Hmmm, I am not a VBA kinda girl. Where would the code go? I will need to hunt down some resources if we want to go down that track... I am not quite sure where to begin.

Thanks Al... I obviously need all of the help I can get!

Lizzy  :)