Cass

Hi Tailboy, Well you sure have received a lot of good advice. Here are two more considerations: 1. While you don't capitalize the m in Filemaker, you do run the two words together. This along with the open file folder gets you very close to copyright infringement. I don't know how much the company protects its trademark, but you might be advised to stay away from this, or else you will have to acknowledge the trademark which further complicates your logo. 2. Your present and future customers are much more interested in what you can do to solve their problems than in the tool(s) you use. You want a logo that identifies you, yourself - not the tools you use. Another reason for dropping the "...Filemaker..." and open folder. Best of luck in your endevours. Cass

Hi Ugo, Thanks so much for taking the time to address my problem. I really appreciate it. Now on to study your solution. Cass

Hi Ugo, I suspected solving this problem will involve a self join but I still don't understand the details. Guess I will have to study self joins. About your last question: All the people who paid in 2002 are the set A. All the people who paid in 2003 are the set B. The people who paid in BOTH 2002 and 2003 are the conjunction of set A and set B (the "overlap" of the two circles of a Venn diagram). The people who paid only in 2002 are a subset of A and the people who paid only in 2003 are a subset of B. I want to find the subset of A that paid only in 2002 - so I can write them a dunning letter. The fields are: Check SN (unique, auto entered); Name SN (which relates to a Names database) and Year (for now the only entries are either 2002 OR 2003 - I just started keeping records) plus fields for the checks: date, amount, check number). Hope this helps in defining the problem. Cass

Guess I didn't make myself clear. To be specific, some NameSNs (i.e., some people) paid dues in 2002, some paid in 2003, and some paid in both 2002 and 2003. If the people who paid in 2002 are Set A and the people who paid in 2003 are Set B, what I want to find are the people who paid in 2002 and only in 2002. In other words, I want to eliminate the conjunction of Set A and Set B.

I have a DB with fields SN (auto entered), NameSN (used to relate to a Names DB), Year and additional fields. An extract might look like this: SN NameSN Year 7 11 2002 8 14 2002 9 14 2003 10 21 2003 I need a find request that will return only records like SN7. Obviously, Find 2002 will return both records like SN7 and SN8. Thanks for any help.

Hi Queue, Thanks again for your interest. Yeah, I'd guess you are a mathematician! I do understand Boolean logic; indeed my solution of setting up flag fields then using them in my final case calculation is based on Boolean (see my last paragraph above). But my mind works in a step-by-step linear fashion. An anecdote: Waaayy back in 1968 (ancient times) while working in the lab as a chemist, I wrote a Fortran program to do I don't now remember what. Used punch cards (don't drop them!). Well, the program did what it was supposed to do but took, as I remember, a half hour or so. Then a properly trained programmer got ahold of it, revised it and the program then ran in only one or two minutes! Anyway, thanks again to you, LaRetta and Lee for your help. It's nice to know such helpful people.

First, many thanks to you who responded. To LaRetta, Yes, all fields are indexed and yes, the post was a direct copy/paste and yes, all records that are not either 1 or 2 are a 3. To Lee, Yes, you are correct, when SNF=1 and FNF=1 and TF=0 it does not return a 3. To Queue, My over-65 brain is to simple minded. No offence, please. Now to all, This question is related to a previous question I posted (which Lee answered) about concatination. It gets complicated (see below) but the short answer is I'v worked around the problem so this and my previous questions are now moot. I really apologize for taking up your time (though as a matter of curiosity, I'd still like to know why the case didn't work). Here is my final solution (Remember, I'm getting old): In my database, I have records with fields TITLE, LAST NAME, FIRST NAME, and SPOUSE NAME. All records have a LAST NAME entry, and MANY have a FIRST NAME entry but the occurance of SPOUSE NAME and TITLE are random. In addition the occurance of SPOUSE NAME and TITLE are mutually exclusive. This leads to the following three possible concatinations (with spaces inserted when needed) TITLE & FIRST NAME & LAST NAME (e.g., Mr. and Mrs. John Doe) FIRST NAME & SPOUSE NAME & LAST NAME (e.g., John and Jane Doe) FIRST NAME & LAST NAME (e.g., John Doe) I created three calculation fields using IsEmpty() to "flag" whether there are entries in TITLE (TF), SPOUSE NAME (SNF), and FIRST NAME (FNF) then calculated the value of a "master flag" field, MF using the case shown above. Then I created another case field, CONCAT. It looks like this: Case(MF = 1,GetField("Title")&" "&GetField("First Name")&" "&GetField("Last Name"),MF = 2,GetField("Spouse Name")&" "&"&"&" "&GetField("First Name")&" "&GetField("Last Name"),IsEmpty(MF),GetField("First Name")&" "&GetField("Last Name")) Amazing to me, it works! See that IsEmpty(MF)? If the case above would have returned a 3, I would have used it there instead.

I have field MF=Case() where case is: Case(SNF =1 and FNF = 0 and TF = 0 ,1,SNF = 0 and FNF=0 and TF = 1,2,SNF = 1 and FNF = 1 and TF = 0,3) This case returns 1 and 2 as it should, but where it should return 3, MF is blank. All fields are number and fields SNF, FNF, and TF all have either a 1 or 0. None of those three fields are blank. Thanks for your help.

Hi Peter, To put my two cents worth in the pot, if you have one file with 250 fields, it looks to me like you have violated some basic rules of relational databases. I see that you did say you are using v4. I'm not familiar with its capabilities and limitations, but if at all possible, I suggest you consider restructuring. Cass Moret

Hi Lee, Thanks for anwering my question! Yes, that's exactly what I want. I was doing this in a spread sheet but since the presence of a title and or spouse name is random, I would have to: First, sort on the Title field (column), then concatinate the records with Title, First Name, and Last Name (Title and Spouse Name are mutually exclusive), then Second, sort the remainder of the records on the Spouse Name field and concatinate Spouse Name, First Name, Last Name, then Third, concatinate the remaining records First Name, Last Name. As you can see, that's several steps (although I got pretty proficient from practice). I expect I could do all this with a script but wow! Somewhere, I saw a method for concatinating without adding redundant spaces but can't remember where. If I concatinate all records the same and have one that doesn't have a Spouse Name, I could wind up with Joe and Doe. I hadn't thought of using CASE but will look into it. Thanks again. Cass

I have fields "Title," "Last Name," "First Name," "Spouse Name." All records have a "Last Name." In various combinations, some records also have a "Title," "First Name," and "Spouse Name." I want to concantinate to produce calculated fields such as: Doe, John Doe, Mrs. John Doe, John and Jane Doe. How can I do that and eliminate unnecesssary spaces?

I just downloaded the matrix model article. A quick scan and I think it's what I need. Thanks much. Cass

Thanks Ugo and Tom for your replies. Here is what I (volunteer, amateur)am actually trying to do for a resident association: Using FMP nomenclature (which I'm still getting used to) I have a DB of street names called, quaintly, Streets.fmp5 It has two fields, Street SN and Street Name. There are only 91 records. The second DB is called Houses.fmp5 and has four fields, House SN, Street SN (for relating to Streets.fmp5) House Number, and a calculated field, addresses (e.g., 745 N. Allyson Dr.). This DB has about 2000 records. The third DB is called Names.fmp5 and has the following fields: Name SN, House SN (for relating to House.fmp5), the calculated address field, and the usual first name, phone number, e-mail address, etc. This DB also has a field called Member which shows whether dues have been paid for the current year. Since all houses are in the same zip code, I can use global fields for city, state and zip. There is also a fourth DB called Checks.fmp5 for recording membership data (check no., date, amt.) but this is not part of the problem. Everything works beautifully! I really like FMP6 compared to other DBMSs I've used in the past. Much easier. But what I want to do is produce a report and a file that I can export that shows member name by street, e.g, all members on N. Allyson Dr. I believe I can figure out how to produce the report if I can link Streets.fmp5 to Names.fmp5. One way to solve the problem is to put a Steet SN and a Street Name field on the Names.fmp5 DB. Since there are only 91 streets, I could manually enter the Street SN and have Street Name be a lookup. Not very elegant. Another way would be to use FMP's many-to-many outer join, but I studied the help file for that and find that cumbersome. Besides, I don't have many names to link to many streets as in the classic class enrollment application (many students in many classes). Finally I actually did the CD case using Borland Delphi. My view shows the CDs. Click on a CD and the user gets a "portal" showing the tracks with related data, click on a track, and the user gets a portal showing the artists with related data. Conversly, the user could search for CDs containing a particular track (song) or CDs containing a particular artist. Unfortunately (or fortunately) I can't use Delphi for this application. If you read this far, thanks for your attention and Tom, I will look up the reference. Cass

New User! I've read that FMP cannot do a one to many with a subsequent one to many but that there is a workaround. Could someone guide me to such a work around? For example, one street has many house numbers and one house number has (may have) many families (though usually not at the same time). One CD has many tracks and one track has many artists. I know in this latter case you can use many to many. :confused: