Our Preferred 5 Star Hosting Provider - Award Winning FileMaker Pro Hosting
Results 1 to 8 of 8
  1. #1
    Forum Member
    Join Date
    Dec 2002
    Posts
    7
    Location
    Brooklyn, NY
    FileMaker Pro Software
    12
    OS
    Windows
    Skill Level
    Intermediate
    Last Post
    11-06-2017
    Local Date
    11-25-2017
    Local Time
    09:32 AM
    Last Active
    11-10-2017

    Finding relational fields

    I have two separate databases. Called one A and one B. They are connected via a key filed. There's a field (call it X) on File A that is either blank or has a text value, one of which could be the word "Both". In file B I place the related field X on the layout so I can see the field value.
    I am trying to find, in File B, the records in File A that has a value of the word "Both" in the X field.
    I do a find in File B on X from File A to see if it's the value "Both". I get 20 records, only 4 of which have the value "Both". In File A if I do the same Find, I get the correct 4.
    Itried looking for =="Both", ="Both", "Both", Both. All getting the same 20 records.
    I'ts driving me crazy. Anyone ever have something similar?

  2. #2
    Good guy doughemi's Avatar
    Join Date
    Apr 2005
    Posts
    1,280
    Location
    Middleport, NY USA
    FileMaker Pro Software
    FMPA 14
    OS
    Windows & Mac
    Skill Level
    Intermediate
    Last Post
    11-07-2017
    Local Date
    11-25-2017
    Local Time
    04:32 AM
    Last Active
    11-25-2017
    We are able to give much better help if you forget about A and B and X and tell us the real names of the tables and fields and a description of the real world problem you are trying to solve.
    Last edited by doughemi; 10-30-2017 at 03:17 PM.
    Give the gift of life. Register as an organ donor today!
    http://www.organdonor.gov/becomingdonor

  3. #3
    Forum Member
    Join Date
    Dec 2002
    Posts
    7
    Location
    Brooklyn, NY
    FileMaker Pro Software
    12
    OS
    Windows
    Skill Level
    Intermediate
    Last Post
    11-06-2017
    Local Date
    11-25-2017
    Local Time
    09:32 AM
    Last Active
    11-10-2017
    I have two separate files, parents and billing. I have a key to connect the two files. That way I can get the parents telephone number displayed in billing. There's a separate field called paper. If it says paper then the parents want a paper bill, not an emailed bill (working on that part now). In billing I put the related field paper and it comes out fine. If I do a find on the related field paper to see all those that have the word "paper" in the filed, I get 50 instead of 11. The 39 records don't show the word "paper", only 11 of them do.
    I just created another filed - paper - in my billing program which is a calculation field that makes the billing field paper equal the parents field paper. When I do the find on the billinbg field paper, it works fine. Why not on the related field paper?

  4. #4
    Good guy doughemi's Avatar
    Join Date
    Apr 2005
    Posts
    1,280
    Location
    Middleport, NY USA
    FileMaker Pro Software
    FMPA 14
    OS
    Windows & Mac
    Skill Level
    Intermediate
    Last Post
    11-07-2017
    Local Date
    11-25-2017
    Local Time
    04:32 AM
    Last Active
    11-25-2017
    I'm still confused. Do you
    • want to start on a Billing layout and show all the parents that desire paper bills?
    • want to start on a Parent layout and create paper bills for the current month?
    • Something else?


    I ask again: what is the real world problem you want to solve (NOT how you are trying to solve it in FileMaker)?
    Give the gift of life. Register as an organ donor today!
    http://www.organdonor.gov/becomingdonor

  5. #5
    Forum Member
    Join Date
    Dec 2002
    Posts
    7
    Location
    Brooklyn, NY
    FileMaker Pro Software
    12
    OS
    Windows
    Skill Level
    Intermediate
    Last Post
    11-06-2017
    Local Date
    11-25-2017
    Local Time
    09:32 AM
    Last Active
    11-10-2017
    The real world problem isn't much of a problem. I have to send bills to our parents, some by email, the rest by snail mail. If I wanted to rewrite the application to be one database, I could probably get it to work well. I don't have the time and there are differences between the parents and their children and the accounts payable databases, which are two separate databases right now.
    I have a key linking them. I'm simply doing a find on a filed in the the parents database from the AP database and getting erroneous results. If I create a calculated filed in the AP DB that basically copies the value of the field from the Parents DB, I can then do a find on the filed in the AP DB and get the correct results. I just think it's a backwards way of doing things.

  6. #6
    Good guy doughemi's Avatar
    Join Date
    Apr 2005
    Posts
    1,280
    Location
    Middleport, NY USA
    FileMaker Pro Software
    FMPA 14
    OS
    Windows & Mac
    Skill Level
    Intermediate
    Last Post
    11-07-2017
    Local Date
    11-25-2017
    Local Time
    04:32 AM
    Last Active
    11-25-2017
    Some details about the relationship would be helpful. It would probably be the only thing that would prevent a proper find.
    Give the gift of life. Register as an organ donor today!
    http://www.organdonor.gov/becomingdonor

  7. #7
    Forum Member
    Join Date
    Nov 2017
    Posts
    1
    Location
    Manila, Philippines
    FileMaker Pro Software
    Filemaker Pro 12
    OS
    Windows
    Skill Level
    Novice
    Last Post
    11-21-2017
    Local Date
    11-25-2017
    Local Time
    03:32 AM
    Last Active
    11-21-2017
    Quote Originally Posted by doughemi View Post
    We are able to give much better help if you forget about A and B and X and tell us the real names of the tables and fields and a description of the real world problem you are trying to solve.
    That's correct! I would ask the same things Doughemi, it could be tough to find solution without the real names

  8. #8
    Senior Moderator - FMT Preferred Developer Consultant - New York - Worldwide AHunter3's Avatar
    Join Date
    Aug 2006
    Posts
    12,187
    Location
    New York and Nassau County, NY, USA
    FileMaker Pro Software
    FileMaker Advanced/Developer 14, 13, 12, 11, 10, 9. 8.8, 8, 7, 6, 5.5 FileMaker Pro 8, 7, 6, 5.5, 5
    OS
    Windows & Mac
    Skill Level
    Developer
    Last Post
    11-22-2017
    Local Date
    11-25-2017
    Local Time
    04:32 AM
    Last Active
    11-24-2017
    Quote Originally Posted by MikeL View Post
    I have two separate databases. Called one A and one B. They are connected via a key filed. There's a field (call it X) on File A that is either blank or has a text value, one of which could be the word "Both". In file B I place the related field X on the layout so I can see the field value.
    I am trying to find, in File B, the records in File A that has a value of the word "Both" in the X field.
    I do a find in File B on X from File A to see if it's the value "Both". I get 20 records, only 4 of which have the value "Both". In File A if I do the same Find, I get the correct 4.
    Itried looking for =="Both", ="Both", "Both", Both. All getting the same 20 records.
    I'ts driving me crazy. Anyone ever have something similar?

    Here is what I think you are saying, and if I'm understanding you correctly I probably know what your problem is.


    You're saying you have a table A which is related to a table B. (they may also be in separate FILES but it doesn't matter if it's a separate FILE or not, the point is they are two separate tables, and there is a relationship via a key field).

    You are on a layout that shows records from table B. And on that layout you have placed the field X which is in Table A. So the field on the layout is from a related table.

    You're trying to do a find on this layout for all records where the value of Table A::Field X is "both". And you are frustrated and confused by the results.

    Is that sounding correct?

    My guess is that for any given single record in Table B (which is where your feet are planted — it's the table of the layout you're on when you do your find) there are multiple related Table A records.

    Like, let's say one value of your key field happens to be "12345". In Table A, if I'm right, you have more than one record with "12345" as the key field value.

    Why is that an issue? Let me draw you a picture:


    Where we are: Table B record................ Related Table A records
    ..................................... first related record has "neither"
    Table A::Field X shows "neither".......second related record has "both"
    as its value on this layout............third related record has "just the first one"
    ......................................fourth related record has "just the other one"
    ......................................fifth related record has "both"



    You end up staring at this record, on this Table B layout, where the value displaying in Field X says "neither" but you did a find for "both"!


    FileMaker includes this record in the found set because even though the first related record from Table A has "neither" in Field X, there are some other related Table A records that do indeed have "both".

    You could see this more clearly if you put a portal to Table A, showing, let's say, 10 portal rows, and put Table A::Field X in the portal.



    ===portal===
    | neither*******|
    | both***********|
    | just the f... |
    | just the s...|
    | both***********|
    ++++++++++
    Last edited by AHunter3; 11-21-2017 at 12:38 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Relational Key/Value Fields
    By ryguy61 in forum FileMaker Pro 9
    Replies: 5
    Last Post: 02-02-2008, 11:08 AM
  2. CDML wl relational fields
    By artengn in forum FileMaker Pro Versions 5 & 6
    Replies: 2
    Last Post: 01-08-2007, 07:57 PM
  3. Lookup issue with relational fields
    By 6killer in forum Microsoft Office & FileMaker
    Replies: 1
    Last Post: 10-13-2006, 04:25 AM
  4. Display 2 relational fields as lists on a layout
    By bubba667 in forum Portal & Relationships
    Replies: 8
    Last Post: 09-01-2006, 09:01 AM
  5. Finding using two fields
    By klowy in forum Find, Sort and Search
    Replies: 1
    Last Post: 04-30-2006, 06:23 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Welcome to the Original FileMaker Forum
With designated forums for almost every FileMaker topic...FileMaker Today is a FREE to join community where you can boost your FileMaker expertise, build better apps & solutions and interact with over 42,000+ FileMaker Developers from all over the world. If you're building FileMaker solutions, this is the place for you.
Follow FMT
We Are FMT
FMT is home of The World's leading FileMaker News Site, Worldwide Developer Directory, FileMaker Connect Community and so much more.