Solved

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

Posted on 2013-05-16
9
462 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
  • 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 24

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
 
LVL 24

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 24

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 24

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now