Script assistance please

[T.J.]

I'm a scripting newbie. I'm just not very good with loops, if else ect. But I keep trying.

I have a table with 4 fields

Data, global_1, global_2 & global_3

I want the global fields to populate with the 1st occurrence of any record in the Data field.

In my script I will sort the information first so that it appears as shown below

Example

Data

A

A

A

B

B

C

C

So I want

global_1 = A

global_2 = B

global_3 = C

The above is just a simple example. I might want to have a many as 12 globals. So I hoping that within the script that there could be a step that will loop and move to the next global record after it populates itself.

Any help would be greatly appreciated.

[Fitch]

You could create a value list called let's say "MyList" that is based on the contents of your Data field. This would give you a list containing one of each value (A,B,C etc.).

Then create a script:

Set variable [ $MyValues ; ValueListItems ( Get ( FileName ) ; "MyList" ) ]

Set field [ global_1 ; MiddleValues ( $MyValues ; 1 ; 1 )

Set field [ global_2 ; MiddleValues ( $MyValues ; 2 ; 1 )

Set field [ global_3 ; MiddleValues ( $MyValues ; 3 ; 1 )

etc

It's possible to do this with a loop, but you'd have to have your globals on a layout with the tab order set correctly, then you could use the Go to next field script step in your loop. But for 3 fields, or even 12, I'd probably just do it without a loop.

The other consideration is that using this value list method, you don't have to sort; the values are always sorted numerically/alphabetically. That's an advantage if that's the way you're sorting, but if not we'll have to do this another way.

[LaRetta]

If the loop is based upon a found set than a value list may not be helpful here but ... well, mostly I wonder if we have a clear picture of your needs?

Can you describe why (and specificly what) you are accomplishing? Because it's very possible that variables might be utilized? You are explaining what you want to do but not why you are doing it. It is possible that you are overcomplicating it (I'm an authority on overcomplicating).

If you are looking for the first occurrence - to populate other records, a small structural shift may solve it for you.

LaRetta

[Reed]

another thing to consider is the fact that you can now dynamically target multiple repititions for both fields and variables, so instead of looping through 12 global fields, you may be able to define one global field with 12 repititions and then loop through them. Whether this fits your need depends on what you're trying to do...

[T.J.]

Thank you Fitch!

Sorry it has taken a little while to respond. This "technique" was exactly what I was after.

[T.J.]

LaRetta,

Thanks for your response. I'll do my best to explain what I'm trying to accomplish

Each new month, I import the prior 3 months of data into the table.

This would be what the data would look like in a table view.

Product# Inv.Date ShipQty

ABC 01/02/06 1

XYZ 01/10/06 1

ABC 02/15/06 1

ABC 02/18/06 1

ABC 02/21/06 1

XYZ 03/02/06 1

ABC 03/07/06 1

What I'm trying to accomplish is I want to see the data presented in this format.

Prod# Jan06 Feb06 Mar06

ABC 1 3 1

XYZ 1 0 1

I was able to come up with a calculation that provided me with MonthYear format.

Left(MonthName (I.DATE) ; 3 ) & Right ( Year (I.DATE) ; 2) = Jan06, Feb06 etc depending on the invoice date.

Where I was getting stuck was how do I get the column headers to change. So next month when I import the last 3 months of data the headers will change from Jan06 Feb 06 Mar06 to Feb06 Mar06 Apr06.

I was assuming that I would need to use a script with a loop to populate some global fields with the MonthYear names.

Fortunately Fitch's technique solved my problem.

I did get stuck a little with Sorting the MonthYear headers properly. I had to create an additional calulation field that displayed the information for Jan06 as 06.01, Feb06 as 06.02 etc. Right (Year (I.DATE); 2) & Month ( I.DATE ) / 100

I found that with Fitch's technique. When you define the value list you can also define a field to sort by. So I want the value list to display the MonthYear (Jan06) but to sort by a numeric representation of the year and month so that they will be in date order not alphabetical order.

Once again thanks for taking the time to inquire on what I was trying to accomplish. I, as I'm sure the many other newbies on this site truly appreciate the time you, Fitch and the many other FM Gurus spend helping us.

[T.J.]

File of my final solution attached.

Usage.zip