FM - How to Create a Long List of Values in a List Layout

Having a brain fart because this must be easy.  I have 118,000 records with various charge codes and want to create a List Layout of each one without duplication.  This is easy to do in a List calc field or a value, but how can I do this on a layout?  I have a couple hundred unique codes, so don't want to do it in a drop-down or tool tip, etc.  Thanks.
LVL 2
rvfowler2Asked:
Who is Participating?
 
Will LovingConnect With a Mentor PresidentCommented:
Filemaker has good options for displaying value lists, it's your requirement that they be in List View which is the problem. All layouts have to be based on a table and List View displays the records in that table. If the issue is primarily that you don't want users to have to scroll through 200 possible codes then think about how you could restrict the list before they get to it.

Two ways you could do that are to have users start typing and use a drop-down menu and the "Auto-complete using value list" option - aka "predictive typing" - so that as the user types the first few characters the list shortens to only those that match. This assumes that users would be familiar enough with the codes to know the first few characters.

Alternately you can setup the Value list to use "related value" and use a global field or two to narrow the list of Codes, assuming they can be sub-categorized into groups. So in one global, display a few options to filter the list by (using checkbox, popup menu, etc.) and in the code field show only the filtered values.

Finally, if you DID use another table, you can simply add a script trigger that runs on the Code field after every new entry. If the Value doesn't already exist in the Code table, it creates the record. The Code table could also have other benefits over time depending on what else you are doing with the codes.
0
 
Andrew AngellCo-Owner / DeveloperCommented:
I'm pretty sure that from the layout you would go into Find mode, put a ! into the field that would have duplicates, and then switch it from "Include" to "Omit".  This should perform a find that omits all duplicates.

Then, of course, you could tie that Find into a script and bind it to a button to load your layout how you want it easily.
0
 
Will LovingPresidentCommented:
You'll need to first generate the list of unique codes. To do that, go to you table with 118,000 records and sort by the code field. Then, go to File -> Export and be sure the click the "Group By" checkbox in the upper right for the Code field. Then select JUST the Code field to export, tab or Excel should be fine. When you export this way you will get only one instance of each unique code.

From there you can create a Code table and then your list view.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Andrew AngellCo-Owner / DeveloperCommented:
Sorry, the method I mentioned won't work for you.  I just tested it and it actually omits ALL of the records that had duplicates, so it won't even leave 1 of the duplicates for you.  I think some method of Finding with symbols would work, though.
0
 
Will LovingPresidentCommented:
If you simply want to find 1 instance of each code amongst your 118,000 records rather than generating a separate table, you can do that by creating a self-join relationship Code to Code. You then create a calc field called FirstRecord using:

FirstRecord = Case( RecordID = SJ_Table::RecordID ; 1 )

This will show a "1" for the oldest created record using each code. On all other records it will be blank. You then just perform a Find for records with 1 in this value. This works because while the self-join is a many-to-many relationship, the first record in the child table is always the oldest (unless the relationship is sorted). The calculation then determines if the current record is also the same as the oldest record.
0
 
rvfowler2Author Commented:
Actually, I really wanted it on a layout so that I can create a Pop-Up window whenever someone wants to look at the various codes.  A drop-down box with over 200 codes would be too combersome.  Looks like the only way to do that would be to export and import into a new table; however, what if a code is added (importing from a DOS database).  Seems FM should be able to handle this without creating another table.  No?
0
 
rvfowler2Author Commented:
In certain circumstances, I will still need the entire list (mostly for Admins).  What about using the method you mentioned above, FirstRecord = Case( RecordID = SJ_Table::RecordID ; 1 ), and then linking a TO by that FirstRecord field?  Then, possibly I could create a layout with a large portal with a scroll bar and make it look like a list layout, but it's just a form layout with a portal on it.
0
 
Will LovingPresidentCommented:
The first record calculation field will be unindexed so you can't create a relationship to it. Also, the fact that the underlying table has 118,000 records may put a lag on display.

I think the other options I gave you will work better, the table plus script trigger one being the simplest.
0
 
rvfowler2Author Commented:
Think I'll go with creating a separate table and creating an add script as in your last paragraph.  Thanks, Will.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.