Find based on a list (excel-hell)

[Bennett Wills]

I am aware of the "one fact, one field" rule in FMP and I'm trying to find out how to a very involved search.

I have a table of 26.5 K records of census-reported occupations from Here. . (It was pared down from 30K records)

As you can see there are a lot of duplicate occupation titles, BUT they have different SOC codes. I imported only the title list because I only need the titles of the jobs when they are being entered (and my data entry people have NO way of knowing what the SOC code an occupation might be. e.g. If someone is an "A Operator" that's all that I can get and expect my co-workers to know.)

Here comes the rub: When we do year-end reporting, I want to be able to say "Find all the occupations that are listed in the SOC '43-' group." or "SOC 43-XXXX sub-group"

How do I search based on a criteria list for 43-XXXX of:

A operator

A.C.P. clerk

Account adjuster

... for a total of 2017 possible values?

Do I make a table of the original data and somehow link the two? Maybe I should make a new table based on the original values, but with the SOC codes, do a find to THAT table and somehow base the find on that list, but I don't know how.

Should I try and do a Filtered Value list where there are no duplicates? as in Finding Duplicate entries

[bdonelson]

From what I understand of the problem, I would start with an additional field calculating first two characters of the SOC Code.

With the search using the new field, would that be the simplest way to accomplish it.

[Fenton]

There's likely more than one method to do any particular task with these. But basically you need a table of both the SOC codes and the Title, with a calculation, as bdonelson says, of just the 1st 2 characters.

You can export summarized to get only the titles, to use for drop-down lists. Or you could just base the value list on the table. FileMaker would make an index of the field, filtering out the duplicates. I don't know if the summarized table/value list would be faster or not. Likely about the same, as it is the index that determines the speed.

You say you only enter the Title in your data. But you could Look Up the 2 characters from the SOC table, via a relationship on the Title.

But what about this?

Account executive 41-3021

Account executive 11-2011

Account executive 41-4012

Oops, same Title, 2 different prefixes. Oh, well, who knows what "account executives" do anyway?

[comment]

I don't understand the problem. If SOC_CODE is a Text field (as it well should be), then searching the field for "43" (without the quotes) will find all codes that begin with 43. I don't see why a calculation field would be necessary or helpful here.

How do I search based on a criteria list for 43-XXXX of:

A operator

A.C.P. clerk

Account adjuster

... for a total of 2017 possible values?

The last part is not clear. Are you describing the desired result for the search (2017 records found), or are there additional criteria for the search?

[Bennett Wills]

DOH!

I thank you Fenton!

The value list does indeed show only one value for the "things" (I don't know what to call it. "tuple"?)

I ran into the "problem" before in reverse when I was trying to show more than one ZIP for a city and FMP was only showing me the first entry for a City or ZIP. I calc'd around it. (ZIP & " " & City --> "Show only values from 2nd Field"

What I'm trying to do is that when someone requests a grouping of placements, how do I show them when the placement record isn't saving the SOC Code, just the Occupation's Name/Job Title?

In Fenton's example he shows 3 possible reports an Acct. Exec. could appear in. How would I associate:

John Smith --> Acct. Exec.

to

___________ SOC Code

Report1 |--> 41-XXXX........John Smith; Acct. Exec

Report2 |--> 41-3XXX........John Smith; Acct. Exec

Report3 |--> 11-XXXX........John Smith; Acct. Exec

??

More specifically, how to I make it stupidly simple to run said report? (Unless it already is and I'm the one who's stupid and not seeing the obvious!) :

I want to thank you all for your help. I am most grateful.

[Fenton]

"Find all the occupations that are listed in the SOC '43-' group." or "SOC 43-XXXX sub-group"

If you are only doing data entry of the titles, there is no way you're going to always automatically get the correct sub-group. You will only get the 1st sub-group that matches the Title. The same would be true for the group, but with less inaccuracy.

In the example posted, for the group, "Acct. Exec" would only Look Up "41", never "11" (or anything else). That is if "43" came first in creation order in your 26.5K SOC rates lookup table; you could change the order. For the subgroup you would always get "41-3021" for "Acct. Exec".

This would of course only be a problem for occupations that had more than one line for a given title in the SOC table.

So, you cannot expect to get better information than you enter as data.

Otherwise you would just Look Up the SOC code into your data entry table. Then also have a calculation to get just the group. You would create a report, with a Subsummary part based on Group, and one based on Sub-group. Then Sort by Group, then Sub-group. Then preview/print.

[comment]

I still don't get it. If the placement record isn't saving the unique SOC Code, then there's no way to group records by different SOC Codes. They are all just account executives, period.

[Fenton]

Yeah, I would agree. If Group "41" is ONLY used for "Acct. Exec", then there's not much point in using SOC codes at all.