Our Preferred Hosting Provider - Award Wining FileMaker Pro Hosting
Results 1 to 7 of 7
  1. #1
    Junior Member
    Join Date
    Aug 2010
    Posts
    3
    Downloads
    0
    Uploads
    0

    Join - Self-reference between records

    Hi:

    I'm working on a patent asset database and am trying to create a self-join so that related assets will be related. Unfortunately, it is only half-working and I'm at a loss.

    For each record in the patent asset table, I'd like to be able to include a portal that includes a list of records from the instant table. I've actually been able to get this to work insofar as I have a join table that will create a cross reference for each cross reference I enter into the portal. However, there appears to be a relationship problem as I cannot get each portal line to provide additional information about the referenced asset. For example, when viewing the record for patent number 5,555,555, the portal correctly indicates that the '555 patent is related to patent nos. 4,444,444 and 3,333,333. If I go to the layout associated with the join table it appears as if there are records in that table that appropriately reflect the cross-reference, i.e. the joint table has two records related to the '555 patent, the first indicating that the '444 patent is related and the second indicating that the '333 patent is related. The GTRR button I've included in the portal works and does take me to the appropriate records.

    The problem is that I cannot figure out how to get the portal row/fields/relationships set up to show additional information about the related records. Dropping a title field from the join table into the portal row doesnít work even if I create a relationship that equates the title fields as between the main and join tables. The field is simply blank in this case. Dropping a title field into the portal row from the table in which the primary resides just gives me the title of the primary key.

    Any thoughts on what I'm doing wrong?

    CJ
    Warning: This is an Old Thread
    This discussion is older than 420 days. information contained in it may no longer be current

  2. #2
    Senior Member
    Join Date
    Apr 2007
    Posts
    1,827
    Downloads
    0
    Uploads
    0

    Re: Join - Self-reference between records

    Not exactly sure how you determine that the various patents are related (and seems filemaker isn't either?).

    However, try creating a new table that will have a value field used to create the relatioships to each other.

    For instance:
    1 John Smith
    2 Sally May
    3 Edison
    4 Etc.

    Now you can create a portal for each patent and assign as many matches as you want.

    Go to a form layout in that New table and drag a portal from the patent table and all of the matching patents will show.

    This assumes a relationship where entities may have multiple patents and patents may have multiple entitites.

  3. #3
    Junior Member
    Join Date
    Aug 2010
    Posts
    3
    Downloads
    0
    Uploads
    0

    Re: Join - Self-reference between records

    Thanks for the reply. If I read you correctly, this is what I've already done. BTW, knowing when a patent is related to another isn't hard, but the data is not in an easy to access form. Hopefully this DB will fix that a bit.

    In any case, in the portal I have created the lines look like the following:

    Line number correctly related patent (input manually at this point) Incorrect or missing title of related patent GTRR (which works)

    I can relate as many of my patents as I want, but I feel I've done something wrong as I cannot get the additional information about the cross referenced record to display itself. Patents are esoteric enough without having to memorize docket numbers.

    I'm likely to create a sand box to work on this problem.

    Cheers,

    CJ

  4. #4
    Senior Member
    Join Date
    Apr 2007
    Posts
    1,827
    Downloads
    0
    Uploads
    0

    Re: Join - Self-reference between records

    Have you tried creating a portal on the parent form layout?

    If you aren't familiar with that go to help and search for portal.

    The portal will list all related records and you can place whatever fields you want on it.

  5. #5
    Senior Moderator - FMT Preferred Developer Consultant - Netherlands, Europe, Asia, USA Maarten Witberg's Avatar
    Join Date
    Mar 2004
    Posts
    5,978
    Location
    netherlands
    FileMaker Version
    FM 11, FM 11 Developer, FM 11 Server, FM 12, FM 12 Developer, FM 12 Server, Older Versions 2-8
    OS
    Windows & Mac
    Skill Level
    Developer
    Downloads
    0
    Uploads
    0

    Re: Join - Self-reference between records

    If your join table is set up correctly, the fields from the tables on either side of the join should display their values as per spec.

    In a portal, the positioning of fields is a bit fiddly, make sure they fall within the first portal row when in layout mode. If you still have trouble making it work, describe how you set this up (or better still, upgrade and post a stripped demo version of your file)
    Stick to what you don't know.

    http://www.maartenwitberg.nl


    -------

  6. #6
    Junior Member
    Join Date
    Aug 2010
    Posts
    3
    Downloads
    0
    Uploads
    0

    Re: Join - Self-reference between records

    I created a small database to check this out and am having the same problems but I am also starting to appreciate the source of those problems, I think. There is clearly something about joins I'm not getting.

    I have two tables: Main and Priority. Main has all of the basic bibliographic information about a patent asset including a unique serial number (asset_id). Priority is a join table that is related to the Main table by means of equating the asset_id field in the main table with a field in the Priority table called "key". The relationship allows records to be created in Priority table by means of the relationship.

    I've included a portal in a layout that shows records from the Main table. The portal itself shows records from the Priority table. I've included another field called "priority doc" in the Priority table and it is this field that is in the portal. The "priority doc" field in the portal is formatted to use a drop down menu derived from the asset_id field and a title field from the Main table. Taking advantage of the features of the portal, I can create multiple priority cross-references in the Priority field. So for example, if I have 100 patent records in the Main table, many of which claim priority from other records in the Main table and some of which claim priority from multiple records in the Main table, I can create these cross references. The Priority table is essentially a list of the cross references that looks like this:

    Key Priority Doc
    50 1
    50 2
    50 6
    75 23
    80 35
    80 38

    This appears to be correct, but I'm starting think that because I've used the drop-down that it only looks correct. What I'm finding is that while I'm getting the connection/cross-reference I'm looking for, I am not seeing the functionality that I'd like to see. For instance, I'm at a loss as to how to get the portal to show some of the other bibligraphic information regarding the priority document. As you can imagine, you need things like titles and filing dates to differentiate patents from one another. Further, for other features that I plan to add, I will need to ensure that I'm picking up additional data about the cross-referenced priority documents. I'm not certain how to do that just now.

    I'd provide you the sample DB that I've worked up, but I don't have a means to make it available to you and I'm not seeing an "attach" button on the site.

    Hope this is clearer.

    Cheers,

    CJ

  7. #7
    Senior Moderator - FMT Preferred Developer Consultant - Netherlands, Europe, Asia, USA Maarten Witberg's Avatar
    Join Date
    Mar 2004
    Posts
    5,978
    Location
    netherlands
    FileMaker Version
    FM 11, FM 11 Developer, FM 11 Server, FM 12, FM 12 Developer, FM 12 Server, Older Versions 2-8
    OS
    Windows & Mac
    Skill Level
    Developer
    Downloads
    0
    Uploads
    0

    Re: Join - Self-reference between records

    to exchange files, you need to upgrade your membership. To clarify, the fee goes towards maintaining the forum. All mods & other helpfuls are volunteers.
    Stick to what you don't know.

    http://www.maartenwitberg.nl


    -------

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Creating Records in Join Table
    By j4jason83 in forum ScriptMaker and Scripting
    Replies: 0
    Last Post: 01-05-2012, 01:32 PM
  2. Searching for related records that contain graphics / self-join
    By polarpro in forum Portal & Relationships
    Replies: 5
    Last Post: 08-09-2009, 04:10 PM
  3. Portal records, stacked, join table?
    By GeoMcK in forum FileMaker Pro Versions 5 & 6
    Replies: 2
    Last Post: 07-31-2004, 07:53 AM
  4. Deleting portal records in a join table
    By John Heffernan in forum Portal & Relationships
    Replies: 2
    Last Post: 11-18-2003, 02:30 PM
  5. Best way for users to join records using a FMP many-to-many relationship?
    By The Bobster in forum Portal & Relationships
    Replies: 1
    Last Post: 09-26-2003, 11:11 AM

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.