Solved

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

Posted on 2013-05-16
9
471 Views
Last Modified: 2013-05-17
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.
0
Comment
Question by:rvfowler2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 11

Expert Comment

by:Andrew Angell
ID: 39172806
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
 
LVL 25

Expert Comment

by:Will Loving
ID: 39172807
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
 
LVL 11

Expert Comment

by:Andrew Angell
ID: 39172823
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Expert Comment

by:Will Loving
ID: 39172892
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 39173124
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
 
LVL 25

Accepted Solution

by:
Will Loving earned 500 total points
ID: 39173179
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 39173210
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
 
LVL 25

Expert Comment

by:Will Loving
ID: 39173223
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
 
LVL 2

Author Closing Comment

by:rvfowler2
ID: 39174997
Think I'll go with creating a separate table and creating an add script as in your last paragraph.  Thanks, Will.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sync a Wordpress with Filemaker 9 550
FM - Sorting Titles 5 73
Printing Portal records 15 146
Problem to file 4 35
Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question