Saving only the unique records?

[hartmut77]

Hi- Here is an example that makes more sense than my last question.

I have 10 records and each record has a field called "color."

The records are entered to tell the color of Pens sold.

The 10 were entered as follows:

Black

Green

Red

Blue

Blue

Blue

Black

Red

Purple

Yellow

Although there are 10 different records. I need to find out only those originals and not the duplicates. and have it that result sent to another file "originals" into a field called

"SaleColors"

The result would be Black, green, red, blue, purple , yellow

I am trying to figure out how to script that.

Thanks

Dave

[-Queue-]

Here's a simple method. You can either keep it as is and set the result to your related file afterward, or you can change gcolors to be a related global.

Pens.zip

[Jim McKee]

Another method:

1. Create a Value List based on your field, "color". Call it "colors".

2. Create a global text field to hold the list of unique colors. Call it "g_colors".

3. Create a script with the following step:

Set Field [ g_colors, Substitute(ValueListItems(Status(CurrentFileName), "colors"), "

[-Queue-]

Good call, Jim! I would think using ValueListItems will be much faster, too, when there are hundreds or thousands of records.

[Jim McKee]

Queue ...

Thanks.

I'm embarassed to show anyone the first solution I came up with! It was similar to yours, but more complicated. I don't know why after spending an hour doing up that solution, which had 3 scripts and about 6 fields, the ValueListItems idea just plowed right to the front of my brain

Weird how that works.

[-Queue-]

Hey, it's okay. My first thought is usually 'repeating fields and ValueListItems'. Then I go off for a while testing all sorts of crazy ideas. Often it's productive, like that variable day of year file I posted a couple of weeks ago (which I think disappeared during the server hack). But I'd probably still be working on a complicated demo for this solution, if 'self-join' hadn't cut its way to the front of my brain queue first.

[Ugo DI LUCA]

Unless the result should appear in the order specified in the original post, I'd second the VLI idea too, scripted.

[LaRetta]

Well now! Isn't it just like guys to complicate things.

Create a columnar report. Place the field in a leading sub-summary plus any fields you want to export). Nothing in the body. Sort by this field (important). Select Export then export Summarize By only (this field). You will get one of each color exported.

Or am I still asleep?

LaRetta

[Jim McKee]

Hi LaRetta ...

Yah, but he wants the output in a field, separated by commas, not in a report ...

Quote:

"Although there are 10 different records. I need to find out only those originals and not the duplicates. and have it that result sent to another file "originals" into a field called "SaleColors" The result would be Black, green, red, blue, purple , yellow"

and that's exactly what we gave him.

"Ours is not to question why, ours is but to do and die."

LOL

[hartmut77]

Thank you - You guys are great.

Both of you are right. I needed the comma separated but I also could use the export to another file. So both of you have saved my day. I thank you again

[hartmut77]

One last thing if you guys get this.

I do need to bring the unique list back into the same file. Can that be done automatically, even without the commas?

Thanks again

Dave

[hartmut77]

Scratch That.

It is solved already from the response of Jim McKee

Thanks Again

I am a little thick sometimes

Dave

[LaRetta]

Hmm, well I suggest you try it. It works. You can export in any format you wish and you will receive one each unique color. It's a trick I learned from Andy Gaunt.

I'll bet you don't ask for directions either!!!

BTW, if you all haven't downloaded his new eBullet demo, you're blowin it! It's can be downloaded at:

FusionDZine!!!

I've been working with it and it's incredible!

LaRetta

[LaRetta]

Okay, sorry! I didn't see 'send to another file into one field' and thought only an export of unique was needed to 'send to another file.'

Major break needed, I think. I'm glad you got it resolved.

LaRetta

[stann]

Hi again

I tried this same formula with a great big file with 4500 records. It didn't put the unique items in the global field at all. Is that because there are too many items?

Set Field [ g_colors, Substitute(ValueListItems(Status(CurrentFileName), "colors"), "

[Ugo DI LUCA]

So it worked on a small set, but not on a larger one ?

I guess the "color" stuff was just an example to get you started. What exactly is the field and data you're trying to catch.

I would be surprised that you've crossed FM limitations in a text field.

Check back your formula. Is you Value list name equal to what is into the quotes ?

[Jim McKee]

Here's a demo file that creates 5000 records and assigns one of 15 random colors to each.

Follow the instructions on the Entry layout.

The "get_unique_colors" script inserts a comma separated list of the unique values in the color field into the g_colors global.

Unless I don't understand exactly what you are trying to do, the procedure seems to work fine.

Ugo: Yes .. I think you'd need a LOT of colors to hit the 64,000 character limit for the g_colors global. And, good point about checking the Value List name in the formula in the script step.

NonDuplicate_List demo.fp5.zip

[Prasad Anu]

hai,

i have a small problem.....

like i have a file which have employee information of Name, user Type, Manager

this manager is also a employee...

basically the user types are defined list as (SuperAdmin, Admin, Manager)

i have record like the below

Name ---- UserType ---- Manager

SP - SuperAdmin - SP (this user is a power user... him self a manager)

A1 - Admin - SP

A2 - Admin - SP

A3 - Admin - SP

M1 - Manager - A1

M2 - Manager - A2

M3 - Manager - A1

when i am creating the users..... there i need a list where if user type is SuperAdmin the manger list will have only SP(the SuperAdmin UserName)

and if the user type is Admin then in manager list will have only SP (While admin will under the SuperAdmin User

and when user type is manager... the list all the Admin User type UserNames

like the example (A1,A2,A3)

Pl help me in this......

Thanks

Prasad Anu

[Prasad Anu]

hai,

i have a small problem.....

like i have a file which have employee information of Name, user Type, Manager

this manager is also a employee...

basically the user types are defined list as (SuperAdmin, Admin, Manager)

i have record like the below

Name ---- UserType ---- Manager

SP - SuperAdmin - SP (this user is a power user... him self a manager)

A1 - Admin - SP

A2 - Admin - SP

A3 - Admin - SP

M1 - Manager - A1

M2 - Manager - A2

M3 - Manager - A1

when i am creating the users..... there i need a list where if user type is SuperAdmin the manger list will have only SP(the SuperAdmin UserName)

and if the user type is Admin then in manager list will have only SP (While admin will under the SuperAdmin User

and when user type is manager... the list all the Admin User type UserNames

like the example (A1,A2,A3)

Pl help me in this......

Thanks

Prasad Anu

Version: Developer v6

Platform: Windows 2000