Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-05-16
9
Medium Priority
?
478 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
Industry Leaders: 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

650 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