Our Preferred 5 Star Hosting Provider - Award Wining FileMaker Pro Hosting
Results 1 to 10 of 10
  1. #1
    FREE Member
    Join Date
    Jun 2011
    Posts
    8
    Downloads
    0
    Uploads
    0

    Unhappy Filtering table based on relationship from another table.

    Alright, I've been staring at this (seemingly) simple task for about 12 hours. I've tried reading a bunch of tutorials and documentation but everything is either outside my scope of knowledge or doesn't quite fit what I'm looking for. It's like I'll pull up a tutorial on how to filter a relationship based list then it will start going into stuff I don't need, or portal views, or some advanced script I can't quite seem to figure out how to edit it down simplified to work.

    So here's the deal. I have "Table A: items that are in stock." Table A contains the fields: "Item#" and "Stock Quantity". I don't want to complicate this by including the stock quantity. Let's just say we can sell everything in Table A. Table B is a file from the manufacturer that includes every product they make, over 100,000 products. Table B includes "item#", "type", "description", "price", "UPC", and a whole bunch of other stuff.

    What I want to do is filter Table B to only show products we have available from Table A. So filter Table B, by matching "Item#" and hide the rest of the database content that we don't sell, which I can take and import to our shopping cart software.

    I've setup "item#" as a relationship for Table A and Table B. However, that's where it ends for me. I'm getting frustrated and can't figure out how to filter Table B :-/, everything seems to go over my head.

    If I were to use MySql statement it would be something like this:

    SELECT a.item,b.item
    FROM table a, table b
    WHERE a.item=b.item

    ^But I can't figure out how you do that in Filemaker pro and display the results.
    Warning: This is an Old Thread
    This discussion is older than 420 days. information contained in it may no longer be current

  2. #2
    FREE Member
    Join Date
    Jun 2011
    Posts
    8
    Downloads
    0
    Uploads
    0

    Re: Filtering table based on relationship from another table.

    Ok, I just want to say clicking around in the scripts I made this Script, which I think would work:

    Enter Find Mode[Restore; Pause]

    Then from specify:

    Action | Criteria
    Find Records | table_B::item: [=_table_a::item]

    But when I run it it just puts that script into the item field and doesn't display the results. Is there another step in the script to display the results?

  3. #3
    Senior Moderator - FMT Preferred Developer Consultant - New York - Worldwide AHunter3's Avatar
    Join Date
    Aug 2006
    Posts
    11,523
    Location
    New York and Nassau County, NY, USA
    FileMaker Version
    FM 9, FM 9 Server, FM 10, FM 11, FM 11 Server, FM 12, FM 12 Developer
    OS
    Windows & Mac
    Skill Level
    Developer
    Downloads
    0
    Uploads
    0

    Re: Filtering table based on relationship from another table.

    Enter Find Mode [] <—— do NOT restore, do NOT pause
    Set Field [table_B::item; table_a::item]
    Perform Find [] <—— do not restore requests here either


    If you do not understand the nomenclature of that Set Field command, the part after the semicolon is where you click "Calculated Result {Specify}} whereas the part BEFORE the semicolon is where you click "Specify Field".

  4. #4
    FREE Member
    Join Date
    Jun 2011
    Posts
    8
    Downloads
    0
    Uploads
    0

    Re: Filtering table based on relationship from another table.

    Thanks AHunter! I'll give that a try :-).

  5. #5
    FREE Member
    Join Date
    Jun 2011
    Posts
    8
    Downloads
    0
    Uploads
    0

    Re: Filtering table based on relationship from another table.

    Damn, didn't work :-/. I get the error "The provided find criteria is not valid. Enter a valid request before proceeding."

  6. #6
    Senior Moderator - FMT Preferred Developer Consultant - New York - Worldwide AHunter3's Avatar
    Join Date
    Aug 2006
    Posts
    11,523
    Location
    New York and Nassau County, NY, USA
    FileMaker Version
    FM 9, FM 9 Server, FM 10, FM 11, FM 11 Server, FM 12, FM 12 Developer
    OS
    Windows & Mac
    Skill Level
    Developer
    Downloads
    0
    Uploads
    0

    Re: Filtering table based on relationship from another table.

    That implies that either

    a) the field type of table_B::item is not compatible with the value being snagged from table_a::item; or

    b) from the context you're executing this, there is no value for table_a::item, either because the item field in table_a is blank or (more likely) there is no related table_a record at all.

  7. #7
    FREE Member
    Join Date
    Jun 2011
    Posts
    8
    Downloads
    0
    Uploads
    0

    Re: Filtering table based on relationship from another table.

    I have set up a relationship from table_a and table_b for both item fields. Both fields are set as Text, because some of the items numbers will labeled something like "SUA123456". Now I can copy any of the values from table_a and find them on table_b, so the data does exist on both. For example I could copy SUA123456 and search table_b for SUA123456 and it will return the row with the item. I mean, at this point, I probably could have copied and pasted each item# from table_a and then copy the row from table_b back into table_a. However, with over a 1000 products, even if it only takes me 5 seconds per-item that's several days of work :-/. Then there's the fact that I have to do this each month, so it's not a very efficient method to do this manually. I convinced my company to buy Filemaker Advanced so we could do just this: streamline the process of updating our online stock.

    So, I hope someone somewhere has a suggestion on how to do this. Could I import the data from Table_A onto Table_B and filter it that way?

  8. #8
    FREE Member
    Join Date
    Jun 2011
    Posts
    8
    Downloads
    0
    Uploads
    0

    Re: Filtering table based on relationship from another table.

    Ok, well I got to get something going. I'm just going to hand search the values from table_a to table_b one-by-one and paste the appropriate row into a new table with the data from table_b. However, how do I cut & paste the fields from one table to the other. When I copy the field from table_b, I have a seperate table now setup called "instock", but when I paste the row it just paste all the fields into the first field of Instock...

  9. #9
    FREE Member
    Join Date
    Jun 2011
    Posts
    8
    Downloads
    0
    Uploads
    0

    Re: Filtering table based on relationship from another table.

    Sorry, one more thing before I try to manually copy and paste the whole thing, which could be a couple days worth of work. When I run this script, it will return my values:

    Enter Find Mode[]
    Perform Find [Restore]

    ^Then just to test I have one item# in the specify field set: (action) Find Records | (criteria) Table_B::item:[PRODUCT12345]

    This will find and return the product correctly. However, I'm not sure how to do a multiple search based off a list of values. I tried making a value list from table_a, but when I try to sort by that value's list it doesn't do anything. So I think it may be easier for me just to copy in all the values into a list or manually create a script that searches each value individually and search that way. I did try importing all the values from table_a to a new field on table_b and running the same search above (table_b::item[item2]) , however it only returns like three entries for some reason, out of a list of about 500 products. So not sure what's going on there, but it seems to find everything in table_b from table_a as long as I manually search the string.

    Is there a way for me to create a script that searches each value I input (like 500 values) and maybe copy each full row into a new table?

  10. #10
    Senior Moderator - FMT Preferred Developer Consultant - New York - Worldwide AHunter3's Avatar
    Join Date
    Aug 2006
    Posts
    11,523
    Location
    New York and Nassau County, NY, USA
    FileMaker Version
    FM 9, FM 9 Server, FM 10, FM 11, FM 11 Server, FM 12, FM 12 Developer
    OS
    Windows & Mac
    Skill Level
    Developer
    Downloads
    0
    Uploads
    0

    Re: Filtering table based on relationship from another table.

    Unless you value your time very cheaply, you should join as a full member and then you can post a clone of your database. You should almost NEVER have to copy and paste ANYTHING in FileMaker; no version of FileMaker since version 2 has had much reason to use anything as clumsy as Copy and Paste to move data around. (And even in version 2 you would have scripted it, you would not be doing it manually).

    There is some simple basic misunderstanding responsible for your problem and it will take a few minutes for someone to look at your file and tell you how to fix it (and post you back a modified copy of your clonefile so you can see it for yourself).

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 11-21-2011, 09:28 AM
  2. filtering & subtotal different occurrences same table
    By janFM in forum Portal & Relationships
    Replies: 0
    Last Post: 07-24-2008, 02:06 PM
  3. Replies: 4
    Last Post: 12-02-2007, 03:55 PM
  4. Filtering through a table occurance
    By moondowg in forum Portal & Relationships
    Replies: 1
    Last Post: 01-08-2007, 07:48 PM
  5. Filtering a portal or relationship based on a calculation
    By rsb87 in forum Portal & Relationships
    Replies: 2
    Last Post: 05-08-2003, 08:50 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 solutions and solve your technical challenges. 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.