Separating information in one table into different legend of a chart

[FMquestions]

Hi,

I have a table fill with different measurement values. These values are characterized as object 1, object 2, object 3, etc. I am trying to create a chart in which the data series are sorted by the object. Specifically, I will have 2 charts. One will present the data from MeasurementA and another one from MeasurementB. Each line chart will have multiple y axises (Y1=object 1, Y2 = object 2, Y3 = object3). I am having a hard time separating this information in a script. Can anyone offer any help? Thanks.

The table would look something like this.

Date Object MeasurementA MeasurementB

10/4/2000 1 134 198

10/4/2001 2 145 201

10/4/2002 3 168 230

J

[comment]

I don't get this, esp. this part:

Each line chart will have multiple y axises (Y1=object 1, Y2 = object 2, Y3 = object3).

[FMquestions]

What I meant is that each chart will have multiple data series with each data referring to an object.

I am writing a script to find the measurement for object 1 and plotting these measurements as a function of the date in one data series. Then, the script will do the same for object 2, object 3, etc. This is the script I currently have.

Enter Find Mode []

Perform Find []

Set Variable [$foundCOunt; Value:Get(FoundCount)]

Set Field [Chart::T1;$$T1]

Set Variable[$$T1; Value:""]

Set Variable[$nextRec; Value:1]

Go to Record/Request/Page[First]

Loop

Set Variable[$$T1; Value:$$T1 & MeasurementValues::RECIST & "¶"] //The problem with this is that all the measurement values are stored into one record. How can I get it to take the next

//value and store it as another record?

Set Variable [$nextRec; Value:$nextRec + 1]

Exit Loop If [$nextRec > $foundCount]

Go to Record/Request/Page [No dialog; $nextRec]

End Loop

[comment]

I am not sure you need a script of the type you are showing.

Does this describe correctly the chart you want?

X-axis: time (each data point is a date)

Y- axis [1]: measured value of object 1

Y- axis [2]: measured value of object 2

Y- axis [3]: measured value of object 3

How many objects are there? If the number is not known in advance, you will need at least to set an upper limit.

[FMquestions]

Yes, that does describe the chart I want. The amount of objects can vary. From my understanding, FM can only hold 12 series so 12 will be the upper limit.

How can I create the chart without a script?

[comment]

OK, I see. Do you have exactly one measurement per object per date?

Also, do you have an Objects table?

[FMquestions]

There is an object table as well. Each object can have multiple measurements and different types of measurements, all of which are stored in the measurement values table.

[FMquestions]

I just want to clarify what I said earlier. The object can have multiple measurements and types of measurements (i.e. area, density, volume). For each date, there will only be one measurement for each type of measurement. For instance, on 1/24/2009, the object will have one measurement for the area, one measurement for the density, one measurement for the volume, etc. For the chart I am creating, there will be one chart for the area, one for density, one for volume, etc. In each chart, the date will be the label series and the different object will be the different data series. I hope this makes it clear. Thank you in advance for taking the time to help me with this. I have been trying to figure this out for awhile now.

[comment]

Yes, this is not as simple as it may seem.

The different types of measurements are not important, since we can solve for one type and the other types will follow. What is important is that each object has exactly one measurement for each date (no more - and no less), else the chart will be drawn incorrectly.

Although it could be done without the looping script, it would require a set of 3 fields for each type of measurement - so the scripted method seems preferable in this case.

Note also that I have assumed that all objects are present in the found set. If this is not true, then another method must be devised to index and label the objects being charted.

ComparisonChartScripted.zip

[FMquestions]

Hi,

Thank you so much for helping me with this. There were some things I did not understand in the script and I was hoping you can explain it to me:

You defined $objIndex to have the value:

Let ([

indexValues = ValueListItems (Get(FileName);"ObjectIDs")]; //how is FM recognizing what "ObjectIDS" is? From my understanding of the ValueListItems function, it will return a list of value of "ObjectIDs" separated by carriage return. However, you don't have the literal string "ObjectIDS" anywhere in the database.

ValueCount(Left(indexValues; Position (¶ & indexValues &¶; ¶&Values::ObjectID&¶;1;1))))]

I have a similar question for the y label in the chart where you used the Get Value function and specified "ObjectNames" as the value list.

I tried following your script but can't seem to get the index values created properly. In other words, the chart only display the label series but the data series and the legend for each data series does not show up. This database contains a lot of sensitive information so I do not feel comfortable attaching it to this post.

Thanks again in advance for all your help.

[comment]

"ObjectIDs" and "ObjectNames" are value lists defined under File > Manage > Value Lists…

[FMquestions]

Of course! How did I miss that? Thank you so much for all your help. I should be able to build on this script from here/ Thank you.

[FMquestions]

Hi,

I noticed that the script only works if I set it to pause at the end of the script. Is there any reason why the chart will not show up unless the script s set to pause first?

Also, I modified the script so that it will compare the different types of measurement based on the script parameter. On the layout, I have 4 different chart, each corresponding to one type of measurement. I currently have each chart set up as a button to run the script once it is clicked. I would eventually like to set it up so that the script will run automatically upon entering the page. However, it seems like I can't get all 4 charts to display simultaneously because the preceding chart will not display the information. Do you think I will have to store these chart in a record?

Thank you in advance.

[comment]

In my demo. I have used local script variables (prefixed by a single $) to store the charted data. These variables expire when the script exits.

You could use global $$variables instead - but then you need to make sure they are cleared before you start adding to them.

it seems like I can't get all 4 charts to display simultaneously because the preceding chart will not display the information.

Could be the same issue?

[FMquestions]

Yes, that was the issue. Thank you.

[FMquestions]

Hi, I am revisiting this topic. I was wondering how to apply this script if the objects are the child of another parent table. I have modified the value lists so that only the values related to the parent record to be displayed. I also tried modifying the measurement values to display only the values related to the parent record but I was not successful. Here are the steps I modified.

Set Variable [$$objIndex;Value:Let([indexValues = ValueListItems(Get(FileName);"ObjectIDS")];ValueCount(Left(indexValues;Position(¶ & indexValues & ¶ ; ¶ & Values::ObjectID & ¶ ; 1 ; 1 ))))]

Set Variable [$$values[$objIndex];Value:ValueListItems(Get(FileName);"Value")]

For both the "ObjectIDS" and the "Value" list, the values were specified to include only related values from the parent table.

I am plotting the chart on the measurement value layout where only the related values for the parent record is displayed. The chart correctly display the right number of objects and number of measurement but is not displaying the correct measurement values. It seems like I did not correctly filter the measurement values list because I would get 4 series of data when I only have 2 series (i.e. 2 object for the specific parent record). Am I doing something incorrectly when I modified the script?

Any information you can provide would be helpful. Thanks.

[comment]

I was wondering how to apply this script if the objects are the child of another parent table.

I am not sure I understand what you mean by that. Why don't you provide an example of the data (perhaps by modifying the file in post #9) and explain what the chart should show.

[FMquestions]

Attached is the modified file. I modified the file so that I am using global variables and to include 2 charts, one for values A and another one for values B. Go to the Galexy layout and go to the first record. Click on the button "Chart ... (Global) w/ parameters", it will take you to the layout with the charts. Everything is displayed correctly. Now if you go back to the Galexy layout, go to the second record, and then click on the button "Chart ... (Global) w/ parameters", you will notice that there is an error in the charts. The chart should only display 2 series. The legend displays the correct series but there are more lines in the chart than there should be. It seems like this has something to do with the global variables not resetting properly when I go from the first record in Galexy table to the second record. Do you have any suggestions? Thank you in advance.

ComparisonChartScripted.zip

[comment]

AFAICT, it's because you're not clearing the $$valueX variables properly. There is a separate repetition of the variable for each object.

[FMquestions]

I thought I cleared them in the beginning of the script with the step: Set Variable[$$valueX[$$objIndex];Value"""

[comment]

No, because at that time you have:

Set Variable [ $$objIndex; Value:"" ]

so you are clearing only the first repetition of the $$valueX variables. However, there are three objects in the first galaxy, so three repetitions of the variables were created:

$$valueX

$$valueX[2]

$$valueX[3]

Perhaps now you understand why I preferred to use local variables.

[FMquestions]

Yes, I see what you mean. However, using local variables would delete the chart as soon as the script ends and I need the chart to be displayed for a summary report.

[comment]

Well, then you must either invest more in clearing the variables, or come up with another method to generate a series for each object.