Array Formula 1

[rwn]

I have data that is entered in the SO_Detail.Ext_Description field. This information needs to then be show uniquely for each value entered. So for example, the data in this field could be: 100.1020.1555.1645. The report should then print uniquely a value of 100, then a value of 1020, then a value of 1555 and a value of 1645.

My formula to start with is (I'm nopt sure what else needs to be part of this formula ):
local stringvar array qList:= split ({SO_Detail.Ext_Description},".");

 

[rwn]

I have updated the formula with the following, but it only displays the first set of numbers vs each number uniquely.

local stringvar array qList:= split ({SO_Detail.Ext_Description},".");
local numbervar uQty:= ubound(qList);
local stringvar mQty:= qList[uQty];
local numbervar qty;
if RecordNumber > uQty then
qty:= cdbl(mQty)
else qty:= cdbl(qList[RecordNumber]);
qty

 

[briangriffin]

Are there always four values in your database field? If not, do you know the maximum number of values that it could have?

 

[rwn]

There is no set limit to the number of values it could be, but most likely will never go beyond 50 values entered.

 

[briangriffin]

What is your backend database? With that many possible values, it may be much easier to address this there rather than in CR.

 

[lbass]

I think you can just use a formula like this:

local stringvar array qList:= split ({SO_Detail.Ext_Description},".");
local numbervar uQty:= ubound(qList);
if n <= uQty then
qList[n];

Create multiple formulas that substitute numbers 1 to n for "n" up to the maximum number of array values there can be. Then place them on the report as desired--horizontally, or in separate detail sections. If in separate detail sections, format the sections to "suppress blank section".

-LB

 

[rwn]

Where is how is n declared? For the following message appears: A number, currency amount, boolean, date, time, date-time or string is expected here.

 

[lbass]

I meant for you to manually add in numbers for n, 1 for each formula up to the maximum possible number of array elements--since you need separate formulas anyway.

-LB

 

[rwn]

I must not be following the numbers are going to be entered into the {SO_Detail.Ext_Description} field, with each number having . prior to the next number. So if there are 4 numbers entered: 1.5.8.2, the report needs to show these values in the Details section, as 1 then another detail line as 5 and another detail line as 8 and another detail line of 2.

 

[lbass]

You can't create new rows based on a value in a single row, so what you would be doing is inserting additional detail sections a to e or however many you need to accommodate the likely maximum number of numbers in the field. Then create multiple formulas, like:

//{@detail_a formula}:
local stringvar array qList:= split ({SO_Detail.Ext_Description},".");
local numbervar uQty:= ubound(qList);
if 1 <= uQty then
qList[1];

//{@detail_b formula}:
local stringvar array qList:= split ({SO_Detail.Ext_Description},".");
local numbervar uQty:= ubound(qList);
if 2 <= uQty then
qList[2];

//etc.

-LB