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


FileMaker: Perform find on unrelated table/field

Posted on 2014-04-02
Medium Priority
Last Modified: 2014-04-04
Hi Experts,

I have a table containing global fields called GLOBAL_FIELDS with one field: G_SEARCH_SSN. I have a layout called SEARCH based on the GLOBAL_FIELDS table with that one field on it. This is the default layout opened with the file is opened.

I have another table called EMPLOYEES with its own layout.

Both tables are unrelated.

I'm trying to PERFORM A FIND (EMPLOYEES:SSN=G_SEARCH_SSN) from the SEARCH layout on a field from the EMPLOYEES table. If found, GO TO LAYOUT (EMPLOYEE) then GO TO RECORD. If not found, CUSTOM DIALOG "employee not found":

IF (CODE(GET(TRIGGERKEYSTROKE) = 10)) Then //enter key
SHOW CUSTOM DIALOG <-- "no employee found"
GO TO RECORD <-- I want to go to the one in the found set

Open in new window

When I try this, I get an error: "this operation cannot be completed because the target is not part of a related table"

How would I accomplish this???

Thanks in advance.
Question by:rmartes
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
  • 2
  • 2
LVL 25

Accepted Solution

Will Loving earned 1000 total points
ID: 39973610
You have two fields in separate tables that have the same name but there is otherwise not connection between them. And it looks like your not setting a value in the Perform Find step. The perform Find Step need to specify a field to perform the Find on and a value to search for. It looks like you specified a field, EMPLOYEES:SSN=G_SEARCH_SSN (though I think it should really be the actual SSN field)  but no value for it. The value typed into Global field need to get passed to the Find request.

To so this, set a variable $FindSSN to the Global field value and then switch to the Employees layout, and set the Find request to search the SSN field for $FindSSN.

However, a much simpler way to do that is simply to have your opener script go to a layout based on Employees table and showing just the actual SSN field. Then:

User Abort [Off]
Enter Find Mode [ Pause ]
then set you script trigger to either Resume script or to start a new script that does the Find and switches to a more complete layout to display the results.

Assisted Solution

historychef earned 1000 total points
ID: 39975331
Given the description of your setup, you don't even need the Find. Relate the two tables on EMPLOYEES::SSN = GLOBAL_FIELDS::G_SEARCH_SSN, then show the data for the related Employee (if any) on the layout containing G_SEARCH_SSN. It's a lot faster, and there's no scripting involved. If you wanted to, you could create a value list of SSNs (taken from EMPLOYEES::SSN), and use that to populate a drop-down list for G_SEARCH_SSN. This would give you the same result, but would only allow you to choose existing SSNs.

Author Comment

ID: 39978085
Thanks guys for your responses:

Will Loving, the reason I don't have the script go into the Employees layout and straight into Find Mode is because it loads at least one record and then goes into Find Mode. I don't want that. It causes the layout to load up slow, especially when your loading a ton of records from an external source. So, I'm trying to isolate the search upon load.

historychef, the value list won't work as I'm connected to an external source that has a ton of records (ton of SSNs). I need them to physically enter search criteria. Also, I'm using a third party plugin from MonkeyBread Software (http://www.mbsplugins.eu/component_SQL.shtml) to run inserts and stored procedures on temp tables I have created in my external source upon searching. So, I need to isolate the search and the global variables. I can't relate them. MonkeyBread Software works really well.

Here is what I ended up doing:

// OnLayoutKeyStroke for my Search Layout
If (code(get(triggerkeystroke)) = 10) //enter key
commit records [no dialog] //saves global value
freeze window
go to layout (employees)
set field (employees::ssn;g_search_ssn)
perform find[]
if (get(foundcount)) = 0)
go to layout (original layout) //search layout
custom dialog // no records found
// perform MonkeyBread scripts
go to record // found
end if
end if

Open in new window

I'm accepting both your answers as you guys were the only ones that replied with helpful thoughts.

Author Closing Comment

ID: 39978087
Very helpful!
LVL 25

Expert Comment

by:Will Loving
ID: 39978239
Well, as I noted in the other question, if you went to a different layout not based on Employees, ran a Custom Dialog to get the SSN (use a Global field somewhere for the Input field), went into Find Mode, THEN switched layouts to Employees you could then run the Find without having to load Employees into Browse.

Featured Post

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
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