Array question I think

I have a single field (extracted from a pdf order) which alternates item numbers and quantities like this:

6-593
8.000
6-641
3.000
6-4212F
4.000
etc.

What is the easiest way to reformat and reimport the data so I can bring it into two fields, one named ItemNumber and the other named Quantity? I’ve tried to manipulate it with arraybuild but not succeeding.

When done it should look like this:

Item Number Quantity
6-593 8.000
6-641 3.00

and so on.

thanks
bk

This cannot be done with arraybuild or any single Panorama tool. To parse this will require a custom program using a loop. There is no “easy” way to do this without some programming skills.

As Jim suggests you will need to use a loop to accomplish your goal. I have some of your sample data in a single field A and use a looparray to move things to alternately fields B and C. Here is the code in the procedure:

looparray arraybuild(¶, "", "A"),¶,element,item
    if item mod 2 = 1
        B=element
    else
        C=element
        downrecord
    endif
endloop

And here is how it looks in action:

Split%20records

Not at all elegant as what Gary wrote for you but coming from a different perspective, I saw this inquiry as a great opportunity to show how you can use Panorama’s almost simplistic English procedure code to accomplish a need. Sometimes the simpler is a great way to go. If you can read English, and understand the beginnings of coding, this will help you see answers in the future do not have to be complex to work wonders in Panorama.

(Do note that the below code works with all Text fields as it is not doing any error checking for pasting into what could be a numeric field.)

FirstRecord
Loop
    Left
Until Info("Stopped")

Loop
    DownRecord CopyCell
    UpRecord Right PasteCell
    Left DownRecord DownRecord
Until Info("Stopped")

Right 
Select «» ≠ ""
RemoveUnselected

After you have copied and pasted this into your Procedure window, Step it through and see how it is just doing what you would do manually except that it is magically fast.

FYI, in Robert’s example you can replace:

loop
    left
until info("stopped")

with

field 1

I am wondering how Barry extracted this information from a PDF file into the database in the first place. I suspect you could adapt Gary’s technique to reformat the data immediately, before it is even put into the database in the first place.

The pdf is a mess as might be expected. I copied the whole pdf and then used tagdata to isolate the portion of the pdf that included item numbers and quantities. Unfortunately a lot of crap still came along, so the next steps included text funnels and other bits of code to strip it all down to alternating lines of item numbers and quantities. I learned some new tricks along the way, too–thanks Jim for the recording feature!

I did try a loop somewhat like Robert’s but something is wrong in it as it keeps throwing errors. I’ll try his (which looks simpler) after the grandkids and puppy all fall asleep this evening.

Quite likely that more complex coding could strip out and correctly format the needed info before the import, but I know that’s more than I can handle. The sad part is that this is the result of one regular customer upgrading their software. Their old pdfs were easy to deal with. The new ones are much prettier to look at and a royal mess when turned into text.

Thanks all,

bk

Here’s a puzzle and a half:

When I start with the first record in the list below, the downrecord command jumps the selection from the first record to the 3rd to the 5th etc. Is it possible for a visible record to
not really be there?

6-593
8.000
6-641
3.000
6-4212F
4.000

This is a known bug that occurs when both the data sheet and a View-as-List window are open at the same time. Close one or the other to solve the problem.

To be very specific, this simple set of directions:

copycell
right
pastecell
left
downrecord
copycell
uprecord
right
right
pastecell
downrecord
stop

starts on 6-593 and stops on 6-641, skipping right over the second record “8.000”.

Wow. Bizarre. Problem solved, thanks!