Solved

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

Posted on 2013-05-16
9
463 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
FM Exporting Pictures In Container 1 968
Filemaker Server 14 - Webdirect NAT access 8 85
View container field on iPad with filemaker go 3 38
send mail from Filemaker Pro 10 4 93
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…
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…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

914 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

18 Experts available now in Live!

Get 1:1 Help Now