• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

simple outer join in FMP6 Mac

complete noob to FMP. I launched it for the first time about an hour ago. Hopefully this is just an application user interface problem i'm having. but, i can't seem to figure out how to do the simplest task in FM. But, i'm stuck with the application. There's no copy of MSAccess here at this location for me to use.

TASK: take two tables with mailing list data, one of which has already been mailed, and remove all the duplicate entries from the other list so that noone is mailed twice.

in access i'd just do an outer join like:
[code]
SELCT * FROM UNMailedlist.company LEFT OUTER JOIN Mailedlist.company
[/code]

then, do some manual tweaking from there.

but, aside from setting up the UnMailelist.company=Mailedlist.company relationship i have no idea what button to even click in FM. it is completely foreign to me. and, the help files don't mention joins whatsoever except in connecting to an ODBC file (which i am not doing...all the data are already in FM tables).

please don't assume anything (like i understand the basics of how FM works). i'm staring at the screen with a blank look. i'd have better luck with a blindfold at this point.

any help for a lost soul?
0
cudaboy_71
Asked:
cudaboy_71
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>SELCT * FROM UNMailedlist.company LEFT OUTER JOIN Mailedlist.company

for a JOIN, you need a JOIN condition...

0
 
cudaboy_71Author Commented:
sorry. i guess i didn't it clear that i'm having trouble with the FM interface.

I understand that i need a Join. But, i have no idea where to put it. the interface is foreign and there is no help for Joins in the builtin help.

in other words, the answer to this question will not be in the form of a single sentence. i need help with where to put the join, and how to reveal the query in FM once the join is made.

like i said, i'm just staring at the screen with no idea what menu or button to click. if i had a manual i'd read it. if there was a helpfile on joins i'd read it. right now i'm in search of a rosetta stone for FM->Access.
0
 
lesouefCommented:
in fm6, in order to use a sql select command, you need to setup a table with the fields to store the fields resulting from the select command via an import action.
so define data base and create the fields to accept data from the sql databse.
now, use file/import, ODBC source (assuming this already exist on yr machine) then use the wizard to make up the sql query or type it directly in the window.
When you execute the query, you'll get a matching window to let fm which field goes where.
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
cudaboy_71Author Commented:
hrm...maybe FM6 is not so robust as i thought it was. All i want to do is take two tables of similar data (addresses) and do some comparison operations on them. they do not necessarliy have to be in SQL (which looks like it will be a headache). is it not possible to have a couple of tables of data and do a lookup based upon some typical DB type criteria like "show records with unique field value 'x'" or "show records with matching field value 'x'"?
0
 
lesouefCommented:
yes, but it is simply different from sql, you do this using simple find commands. and you write a script when the manual usage becomes too complex.
first you must create  a relationship in between them (kind of permanent join), then place the fields you need in a layout, then use them to search data. f you start from scratch, it may be worth using another tool you are familiar with like excel?
0
 
JoJohn2004Commented:
Go to define fields and make sure you have some sort of unique Key. This will have to be the same in both databases for matching records. Then in the table that has the larger number of records (LargeList), create a relationship to the other table (AlreadyMailed) based on the unique field.

Once you have created the relationship enter layout mode on any layout in the LargeList table and put the Unique Id Field from AlreadyMailed on it.  Enter the find mode and click on the new field. Put  >0 in the new field and then click on the omit button along the left edge. Press the enter key. The resulting group of records have not been mailed.
0
 
cudaboy_71Author Commented:
excellent instructions. exactly what i was looking for. thanks to the other commentors for some insight into how FM works. but, JoJohn's step by step instructions gave me the results i have been after in less than 2 minutes.

many thanks.
0
 
JoJohn2004Commented:
You are certainly welcome.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now