Help on arrays, lists

Panorama X
1

I don’t need someone to solve this for me, just point me in the right direction:

I have a field that will be in the form of an array like this:

a,b,c;d,e,f; etc. I want to display the contents for each record in a columnar list like this

a b c
d e f etc.

I know how to make a text list in which the columns contain different fields for all the (or the selected) records in a DB. What I can’t yet figure out is to make a list out of this field from just one record, to display on a form displaying that record.

An extra bonus would be that if any of the array elements were a number I could sum it at the end. I think that would involve extracting that element from the array in the field, converting it to a number, etc.

So it seems like my question is about dealing with an array that is a constant within a field rather than constructing one from the whole DB (or a selection within it).

I’ll appreciate any hints that put me on the right track; it seems that this should be pretty simple.

Thanks in advance,

2

There you have an array within an array. The semi-colon is one separator, the comma is the other. But you don’t need to use any array functions.

replace(“a,b,c;d,e,f;”,“;”,¶) gives you:
a,b,c
d,e,f

replace(replace(“a,b,c;d,e,f;”,“;”,¶),“,”," ") gives you:
a b c
d e f

3

Many thanks, Jim, I thought it would be simple. Now if the second element in each set is a number, how do I get it out of there to sum it?

Bill

4

I thought you said you didn’t want anyone to solve it for you… :smirk:

IF you’re saying that it would be
“a 6 c
d 7 f”

Then

arrayfilter(“a 6 c
d 7 f”, ¶, {array(import(),2," ")})

gives you

6
7

and

arraynumerictotal(arrayfilter(“a 6 c
d 7 f”, ¶, {array(import(),2," ")}),¶)

results in 13

5

I finally had time to get to this, and once I understood all its elements, it does exactly what I need. Along the way I’ve learned a lot, having to convert from Jim’s example to my actual situation rather than just pasting in a recipe. Thank you, Jim, for your teaching!

6

Ok, back to the beginning:

This is the array that is my data set:

1/1/16,$300,4;2/1/16,$350,4.5;
And so on.

I can cobble up a quasi text list using the replace( function as Jim Cook suggested in his first reply; but I’d really like to use a text list object so I can format it nicely with alignment tags and so on. I fooled around a lot with arraycolumn( but that gave me just one line in the TLO with, for example, all the dates in one TLO grid space separated by ;s, and all the amounts in a second grid space, also separated by ;s, etc. I tried replacing various things with cr() or paragraph symbols, but I can’t figure out how to get the successive rows of the array into successive rows of the TLO as if they were different records in a db.

Is there a way to do this?

7

To display this data in a Text List object you need to replace the semi-colons with carriage returns and the commas into tabs. In Panorama X, one replace( function can do both of these replacements with one function, like this:

replace(... data formula ...,";",cr(),",",tab())

Of course, there must be no commas or semi-colons in your data, but I assume you’ve taken care of that or you would have other problems.

8

I thought I understood this, but apparently I don’t. The array I am trying to display comprises the contents of a field called Target. My most recent question shows the first two “lines” of that array. From Jim Rea’s reply I assumed that the TLO would be displaying a 2 dimensional array, the columns of which are separated by tabs and the rows of which are separated by cr’s. If that were true, it would obviate the necessity of using any array functions at all on the data in the field, which seems logical, since the field contains a 2-D array to begin with.
If I use the formula
replace(Target,";",cr(),",",tab())
in a TDO, I get such a display (but without the possibilities of tag formating); but if I use that formula in a TLO, I get the first line of the field (that is, the values up to the first semicolon) in three columns on the first line of the TLO, and nothing at all on any of the subsequent lines, where I expected the second and subsequent originally ;-delimited elements.
Obviously my assumption is wrong.
Can someone straighten me out?

9

Remove the tilde from the Text List Options panel for Database and be sure Database Navigation is nnot checked.
image

10

Bingo! Thank you!

11

This part of your post seems to have been lost. If you have an array like this:

A = “1,4,567,834,890”

you can sum the elements with:

execute {B = } + replace(A,",","+"))

which is the same as B = 1+4+567 etc.

12

Thanks, Michael. Actually, Jim Cook pointed me to arraynumerictotal( two weeks ago, but I can see that your way would also work well.