Solved

How to Search Table for Related Records in the Same Table

Posted on 2010-11-10
11
685 Views
Last Modified: 2012-05-10
I have a table of documents. These documents come in, are reviewed, comments are posted and revised documents are posted.  All these documents should be in the same table.

Documents are named by Task Order and Document Type:
ABC-INTRO-001-01
ABC-INTRO-CST-001-01
ABC-INTRO-002-02

When I scroll on any of the documents with ABC-INTRO, I want to see the list of the other document names and their due dates in a portal.

Each record has a field called Task Order and Document Type so each record above has ABC-INTRO in the field.

Nothing is showing up in my portal.
0
Comment
Question by:Alex972
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 25

Expert Comment

by:Will Loving
ID: 34106884
I'm assuming that "ABC-INTRO" is the "Document Type"? The relationship needs to be a Self-Join relationship - meaning between two table occurrences of the same table. The Key field on both sides of the relationship will be whatever field holds the "ABC-INTRO" value. This must be an indexed field, so if you don't see "Indexed" next to it in the field list, check the Storage options under Settings.

Are you looking at the documents in List View with a portal to the related documents?
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 34107547
I copied this from your other question which I suggest you delete. Please be sure to post your response with the original question.

>  I should have mentioned this in the question, but I forgot that sometimes ABC can
> be later in the name. When I use ABC*, it also gets XYZ-ABC-001-01.
> When I use ABC*, I also get the ones with XYZ-ABC-001-01.

Unclear.  you indicated that you were not getting anything in the portal at all. If you want to have a key that can match ABC wherever it may appear in the string then you will need to make a calculated key that gives multiple values to match on such as "XYZ", "XYZ-ABC", "ABC", etc. You need to determine what values you do and don't want to see.

To clarify, please describe which values you want to match on
0
 
LVL 1

Author Comment

by:Alex972
ID: 34124569
Sorry if that question about ABC sounded like it was about the portal. That is different. That is just a straight find for a field beginning with certain text.
I have accepted a solution for it.

To avoid that confusion, I am going to give some different examples.
Imagine that you are working with documents from different companies.
The companies are IBM and BP

The document types are Annual Report (AR) and Stock Filing (SF).
Each document type has different versions: 1, 2, 3, 4, etc.

So, the data would look like:
IBM-AR-1
IBM-AR-2
IBM-AR-3
IBM-AR-4
IBM-SF-1
IBM-SF-2
IBM-SF-3
BP-AR-1
BP-AR-2
BP-SF-1
BP-SF-2
BP-SF-2


I have a calculated field that ends up with the company and the type of document, such as IBM-SF, BP-AR, etc.

If I go to IBM-SF-1, I would like to see in the portal: IBM-SF-2, IBM-SF-3, IBM-SF-4 because the portal will be returning IBM-SF records, but I don't need to see the current one.

Since I am on IBM-SF-1, I don't need to see that one.

If I go to IBM-SF-2, I would see IBM-SF-1,IBM-SF-3, and IBM-SF-4.

and so forth.


***Since the Company and Document Type field is calculated can I index on it***



0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Expert Comment

by:Will Loving
ID: 34125043
On the document side, you have a field already with the DocumentTypeVersion in it (IBM-AR-2). You need to also create a second calc field with just the DocumentType (IBM-AR)

On the viewing side there are several ways to do it depending on how you want to select/filter documents. The attached file shows one way which involves using two global fields that get set when you click on a larger list of documents (not shown in the example file). You could modify this to have a global field for Company and Report type and then filter the list of report using calculation fields as the key instead.

The point is that you need a two predicate relationship. The first part says "Match on the document type (e.g. "IBM-AR") and the second says DON'T match on the specifically selected document, e.g. (IBM-AR-2). This will give you all the ones except the currently selected.
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 34125088
The file did not upload. I'm trying again... document-select.fp7
0
 
LVL 1

Author Comment

by:Alex972
ID: 34132145
Looked at DocumentView, but it listed 0/0 Found(Unsorted) and it did not allow me to move through records.
Document-Select.gif
0
 
LVL 1

Author Comment

by:Alex972
ID: 34132274
Attached is a file that I created that actually does what I was looking for.
DocumentTracking.fp7
DocumentTracking.gif
0
 
LVL 1

Author Comment

by:Alex972
ID: 34132276
Actually, there is problem, with layout. Will correct and upload.
0
 
LVL 1

Author Comment

by:Alex972
ID: 34132286
This one is correct.
DocTracking.gif
DocTracking.fp7
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 34132353
Your file does the same thing and uses the same relationship technique that my example files does, the only difference is that you did it as a self-join to the same table. I did it with two tables because I assumed that you might be viewing the documents from a layout based on another table such as a contact record or an interface file that contained no real data at all.
0
 
LVL 25

Accepted Solution

by:
Will Loving earned 500 total points
ID: 34132364
I just looked at the example I uploaded. I'm not sure how it happened but the one record in document view somehow got deleted. There has to be at least one record present for a relationship from a global field to work. You'll find that if you simply add a record the example file I sent that it does work.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
FileMaker Pro Table Relationships Issue 7 286
FM - Design Issue 6 151
delete multiple records in filemaker pro 10 2 397
Filemaker 14 vs Delphi Embarcadero 7 242
Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Suggested Courses

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question