Set definition

[Terrymorgan]

Using a one to many set definition link is it possible that either of two fields in the parent to match the same field in the child and if so how is this achieved
Thanks
Terry

[Tom Patten]

Tell us more. I'm thinking a calculated field in the parent would do it, but need more info to suggest the calculation.

~ Tom

[Ted Giles]

Try linking on an expression using OR

[Terrymorgan]

To give more explanation.
Parent has field "UNIQUEQUOTENO" with one to many link to a field of the same name in child, the child records are shown in an embedded browse on a form.
Parent also has field "PREVOUSUNIQUEQUOTENO" i.e. this is the previous quote record for the same customer
In the embedded browse on a form i would like to display the child records for both Parent fields.
I hope this helps with a solution

Using expression UNIQUEQUOTENO.OR. PREVOUSUNIQUEQUOTENO gives the following error:-
Parent Expression:Argument is incorrect data type.

Thanks
Terry

[Ted Giles]

You can add the same table in a set more than once.
As I read your post, you want to display

Unique in table1 linked to child and
Previous in tabe 1 also linked to child.
On a form.

Is this correct?

Can you post an image of the Set design as it is at the moment please.
Also, an idea if the table structures would help.

It's all dooable but depends on what you are working with at present.

[Terrymorgan]

The set structure is currently:-
PQ set structure.PNG
If a second link is created to the "quotenotes" table using the "PREVOUSUNIQUEQUOTENO" you are unable to show both "note" from each link in a single embedded browse.
What i need is to create a single link that selects all the records in "quotenotes" that match either of the 2 fields in the parent table. I have tried Expression as the Link Expression type but this does not accept ".OR."
Thanks
Terry

[Tom Cone Jr]

Terry, I think your set is upside down. In a new set try making your customer record the parent and the quote records the one to many linked child.

[Terrymorgan]

This method has been used for 20 plus years as, they are not actually customers but prospective customers and as prospects they often come in, in different names albeit that the ultimate customer would be the same.
Its nuisance but too late to change.

As you are far more knowledgeable than I do you think this would work:-
Add to both table an identical fileld, that is populated identically
Link on this field as one to many but then apply a filter to this link
I am not sure if any issues would arise.

[Tom Cone Jr]

I may be missing something, but I don't see how your idea would help.

[Ted Giles]

I have it working so that a NAME in the Parent shows all CHILDREN where a possible link arises.

Turning the Set upside down does the reverse.

I will post an example later, but am thinking along the lines of creating a Summarised Table which combines data from both tables.
Might be easier than using a Set in this instance.
I'll post that too.

[Terrymorgan]

I tried my idea but the filter only filters the records in the child, but cannot base this on a value in the parent.

[armasoft]

I assume there must be a point in your process where the PREVIOUSUNIQUEQUOTENO is entered into the newer record. At this point I'd suggest duplicating these records in the QUOTENOTES table adding the new QUOTENO and possibly with a field to hold the previous quoteno if needed

[Ted Giles]

Try the attached Terry.
It has a Parent with 2 fields - original name and Optional Name.
The Child has just 0ne name.
All the data relating to EITHER record is displayed.

Based on what Jon said, above, it might be useful to have an ALIAS which is constructed from data in the tables when quoting. That's a different operational issue though.

What I did;
Create a Set with the Parent connected to the Child twice.
Each instance of the connection relates to a different Parent field.

You'll get it.

joinup_01.zip

[Mike Wilson]

Hi Terry,
I'm with Tom on this but Ted provided another approach, is to use a second instance of the child table linked to the previousuniquequoteid. Look at what I have. I'm also wondering if this is what Ted was really thinking of.