List of unique values

[LaRetta]

Sometimes I need to quickly find the unique contents of a field. I've (just now) borrowed (stolen) an idea from Matt P. which uses two fields (Input and Output) and Evaluate(). This is for values usually under 100 or so.

It's a time-saver for me because prior, I would have to create a Value List and a calculation or grouped report. But do I even NEED a value list?

If I type into input: Evaluate(ValueListItems(Get(FileName) ; "any VL")) it works great. Is there a way without creating a value list? It's too bad CTRL-I can't be used or even Insert from Index. Again, probably something simple I'm missing. This is only for on-the-fly developer needs etc while we work at normalizing many fields brought in from other programs.

I don't particularly want to create value lists because this data will probably be moved elsewhere after identification (and the field deleted). Ideas?

LaRetta

[comment]

Do you mean find the unique contents of any field, from any table?

[Vaughan]

A summarised report, based on the field, will also give a list of unique values.

[LaRetta]

Hi Vaughan! Thank you yes. But then I delete the report again - it's rarely the same field or table that we wish to view (and I refuse to create a grouped report for every field in every table). And I want to eliminate the need for a Developer every time they want to see a unique list of a certain field. Same with creating calcs (based upon value lists) or even hard-coded scripts. Just to provide unique?

8 has so many more goodies that, with 7's Evaluate() (and maybe some design functions) it might now be possible. I can perform CTRL-I now and SEE all the values; I can Insert from Index and SEE all the values but I can't get AT them without additional energy expenditure and FM can and I envy (and resent) it. I would like a lean, generic process hopefully browse mode User-friendly selection style (specifying file, table and field). I think globals can do it all here although we still may need to create a value list of the field (I don't mind opening value lists to management) and VLs take little resource. And I'm not against scripting but it must be generic and lean in nature. This isn't a one-time need ... I have needed this ability probably 60-70 times easily over the past three years (in various businesses) particularly when migrating and normalizing data. So I have created 60-70 value lists, reports and/or calcs which were just deleted again. Inefficient ...

Michael, I put this Evaluate() technique in my Developer table and can now quickly capture these lists (IF I create a value list of the field first). Even this way, it's nice. Management usually wants the list in text form so they can squabble amongst themselves and mark it up. These 'quik peeks' into the data would be great for normalization reviews as well. Ideally, I would like to have this in my personal Developer file so that when I attach my file to any solution I'm working on, I can rock n roll. I realize I'm really reaching but then again ... I reach because I can. Ideas on streamlining this idea would be greatly appreciated.

LaRetta :wink2:

[comment]

I don't quite see the need for a user to have this kind of access.

For a developer... you can try the attached file, but you'll have to create relationships instead of value lists - a lot of them. I believe you can do this in a separate file, but I haven't tried it myself.

ShowUniqueValuesCF8.fp7.zip

[LaRetta]

Thank you for the file, Michael.

I've been studying it and it seems to require more work (as you've indicated), which defeats my purpose (creating cartesian to each table and adding the fields onto the Fields layout). Also, if I add a new field onto Fields layout, the only way I can get it to show in FieldsVL and cAllValues is if I changed them both to unstored then back to indexed (adding a trigger didn’t allow it to be indexed). And NO WAY would I let Owner into a process such as this. But it does work a treat once in place! Using it as an attached file also works.

I have not given up on implementing this type of functionality and I've actually gotten many more ideas from it!! But for now, I’m considering the attached process instead. It is much simpler and doesn't even require CF so anyone could use it. I may only use this process for myself. But I may make this available to the Owner as well. As long as he doesn’t change or delete any existing value lists, it shouldn’t be a problem (that I can see). It only requires a value list and they take little resources.

I see two additional potential problems based upon the size of the field data selected: 1) on Windows, it is possible to overload the clipboard and cause Windows to lock up and 2) might I hit the field size limit? I can solve the first problem by NOT allowing clipboard and instead exporting field contents. Clipboard was the Management request. I might possibly just warn them to close all other programs and be prepared for a crash if they are selecting a Comments field, for instance. But I have no idea how to count the length of the results of a value list BEFORE it implants in the field - or even how to test whether it accepted the ENTIRE result by testing the last entry in the gResult field!!

I would appreciate any advice or ways to improve upon this. Sometimes providing owners with a request ... and protecting my solution bump into each other. I might go ahead and create a value list on most fields so they will have no need to enter Value Lists at all.

UPDATE: Actually, I might create a value list called FIELD EVALUATION or something so I can specify 'use values from field' and 'all values' and just ask Owner to CHANGE the existing value list instead of creating new ones (less chance he'll make a mistake on it and I won't need to delete multiiple VLs if we end up deleting the fields). But I might just remove this option entirely...

UPDATE 2: I still may go your method ... if I place all fields on the Fields layout, then open the field definitions FIRST, it'll be in place and always work. And THEN Owner could use it also because there would be no need for him to get into Layout Mode or field definitions ... but I have several hundred fields throughout all my tables. Yikes! I don't feel I can plan ahead on it very well ... that's why I'm looking at this alternate method.

LaRetta

UniqueBrowse.zip

[comment]

I don't think there's a way to do this without spending considerable resources and effort. Now that I've seen the hoops your user must go through, I am thinking: why don't you simply script this entirely, using perhaps a table of possible fields (and their tables), then doing a Fast Summary loop on the selected field? The result can be shown in list view, as a found set, with no consideration for field size.

[Razumovsky]

Hi L-

Here is a little script that isnt too time consuming to implement. You only need to add one calc and one dummy layout for each table (and update the pattern count case statement in the go to layout scriptstep), but not so bad... however, does not address the copy/paste concern, and the cValueField calc needs to be adjusted to swap carriage returns for a text character or else they would read as multiple values in a single field, but you get the idea...

-Raz

(I agree though, there should be a way to do this without a script- If FM can, it is only fair that we can as well.)

UniqueBrowseScript.fp7.zip

Edited March 19, 2006 by Guest
forgot initial commit record step...

[comment]

Ahem... what about addressing the unique concern?

[Razumovsky]

Er, unless I am completely misunderstanding the post (wouldnt be the first time), I thought I did...

Perhaps the tAllValues text box is causing the confusion- I left it prominent on the layout to keep the guts a bit more visible in the demo, but the unique values appear in the right hand cUniqueValues field that builds a value list out of tAllValues.

[comment]

Ah.

[LaRetta]

HI Razumovsky,

Thank you for the demo! Yep, I've played with methods such as this. That global calculation in each table (by its very nature of being stored) will not update when a new field is added so cAllValues (even though unstored) will never change.

I spent all night on the Fast Summaries method but can't figure out how to get a user-selected field be the sort field without hard-coding every field in a sort. I haven't given up on the idea ... I just can't make it work dynamically the way I would like. Michael, I tried creating table/field table but I still hit the sort issues. I don't mind adding a field to this table each time I add a field in my tables but I don't want to script a sort on every one of them. And my first Fast Summary test took almost 2 minutes; using the VL approach took 2 seconds (125,000 records).

Bottom line (to me) is this ... since I will be in field definitions when I add a new field anyway, I can immediately create a value list out of it (if I or management or even users might need to see unique on it). I will preceed the VL name with "-" so I know it isn't being used in the solution except for this purpose. This will eliminate my fear of opening the value lists to management. I would rather create a simple value list and be done with it - they take very little resources. The process will update itself the moment a User first uses the new field. And I won't create a VL from a comment field - I scroll the actual data on THOSE fields. So unless I can figure how to make Fast Summaries method dynamic (and faster), this is the way I'll go. I will test the result field using Length() and warn them (and me) if the size reached the limit of ummm, what is the character limit for text fields again? And where are those figures listed in Help? I searched for 45 minutes! I want those figures in my Developer file so I'll always know in an instant.

LaRetta

[comment]

how to make Fast Summaries method dynamic (and faster)

For dynamic, use GetField(). Of course, that's not going to make it any faster...

[Razumovsky]

Yep, I've played with methods such as this.

I should have figured you must have started off with that...

This is a tough nut to crack, and seems to lead back to the old stored calcs referencing related fields issue. I have eaten up countless hours of my time in search of that grail, and have recently begun to come to terms that it is a myth. Perhaps FM8 might offer some new leads along this line (in spite of my commitment to upgrade, I still havent gotten around to it), but, If Comment is stumped, that does not give me much hope!

What about an old school replace key ID with key ID script step to facilitate indexing? Although you probably tried that route as well...

-Raz