Self Join Based on related Field Match?

[Shane]

I want to do a self join portal in a file that uses a matching field that is related from another file. I tried to make a calculation field using the relationship to generate a field, the result on which to base the self join match, but FM warns me that that wont work because the result cannot be indexed, because it comes from a relationship. How can I do this?

[mattlight]

If you wish bring up information from the related database, you will need an Identical phrase or keyword in both databases (Common ground).

Example.

I want to automatically show all unpaid amounts from my invoices database from within a portal in the orders database(so that I can check current balance before proceeding with a new sale).

I make a field in the invoices database which is a calculation (text result)

if(payment amount < amount due, "Unpaid" & "-" & client ID, "Paid in FULL" & "-" & client ID)

this will generate a field where the result looks like:

Unpaid-XX1234 or Paid in FULL-XX1234

Now, in the Orders database, create a field which is a calculation that will generate the same result but forcing the word "Unpaid" at the beginning.

calculation:

"Unpaid" & "-" & Client ID result = text

The common ground field between the two databases is that they refer to the same Client ID (either by a lookup or it's manually entered the same)

Then create a relationship between the two databases, between the two new fields.

The calculation in the orders database new field does not have to be forcing the "Unpaid". Create a field that uses a pull down menu value list with custom list - "Unpaid" [return] "Paid in FULL" and put this field in the calculation within orders instead of "Unpaid". Then you can, at a push of a value list, see what they have and haven't paid.

Using this principle should work for you.

Full credit goes to Rob Weaver for explaining it to me once upon a time, it's just MY way of doing a COMPOUNDED search field.

The second you put a related field into a calculation it will remain unstored calc and cannot be used for relationship. Only use local fields and then 9/10 should be alright.

eiyte?

[ November 08, 2001: Message edited by: Matt Lightbourn ]

[ November 08, 2001: Message edited by: Matt Lightbourn ]

[Shane]

Matt,

what I want to bring up in the portal is other records in the

same file based on a value in a field that comes from an existing relationship.

I have a manually entered field that defines the relationship that generates the value in the field that I want to use as the match field for a self join relationship portal

example:

I have a product design control file which contains customer style numbers and customer design numbers. I may have multiple records with the same style number, but the design number is unique.

I have a Product Manufacturing file that relates to the Product design control file by the customer design number. the Style Number is shown through the Design Number relationship (The Design Number is entered manually). In the manufacturing file I want a portal that shows all of the manufacturing records that share the same style number. I am trying to do this with a self join relationship shown in a portal.

I think what you are showing me is a way to bring up information from the related file.

In the manufacturing file I have fields that define the different parts used to assemble the final product. and they change color according to the design number. I want to show the manufacturing people the parts that they need in which colors to assemble multiple design numbers for a style number order.

Also I may have multiple Manufacturing files for different processes and vendors.

[LiveOak]

Why not perform the display in the product design control file or use a script to set a global to the value of the design style and defind the self-relationship based upon the global or use a lookup to bring the design style number into a non-related field?

-bd

(By the way, we don't allow duplicate posts. I'm deleting your other posts.)

[ November 08, 2001: Message edited by: LiveOak ]

[Shane]

Sorry about the duplicates, I guess I didn't read the rules very closely. I have gotten some great help from these forums. They are a wonderful resource that I would like to continue to be a part of.

Anyway, I don't want to display the values in the design control, because it's really the main file, and there will be different Manufacturing files depending on what kind of product it will be.

can you give me an example of what a script to set a global to the value of the design style would look like?