Relationship between DB's depending on 2 Field's

July 23, 200322 yr

I have 2 DB's that contain info about customers, conveyors, widths, lengths, thicknesses ect.

DB1 has ALL the above mentioned fields.

With DB2 i want to be enter values into the Customer and Conveyor FIELDS only, and IF there is a EXACT MATCH of the CUSTOMER & CONVEYOR fields in DB1, then rather than having to enter all the other field values into DB2, i want the Values to be taken from DB1 and AUTOMATICALLY placed into the correcsponding fields in DB2.

EG.

DB1:

customer=fred

conveyor=C100

length =100 Mtrs

width =1200 mm

thick =10 mm

DB2:

customer=fred

conveyor=C100

length =

width =

thick =

After entering customer & conveyor there is a match in DB1.

So then the length + width + thick FIELDS in DB2 will have 100 + 1200 + 10 values taken from DB1 and entered into DB2 to save having to RE-Enter ALL data each time.

ONLY Customer and Conveyor needs to be entered.

I am using File maker v4

Any help is appreciated.

July 23, 200322 yr

Hi,

Create a Compound Key CUSTOMER & " "& CONVEYOR in both files.

Create a relationship CompoundKey::CompoundKey and set the widths, lengths, thick to be lookup fields using the above relationship.

If there's a matching key, they would auto-fill, if not, you would fill them manually.

July 24, 200322 yr

Author

What is na dhow do i set up a COMPOUND KEY ?

i am using V4 and have NOT heard of a COMPOUND KEY before.

Corey....

July 24, 200322 yr

Sorry for this Corey,

Compound Key, Candidate Key or Concanate Key (may be other terms) is a Key using a Concanation of 2 fields.

In this case, 'Customer' and 'Conveyor'

That's why I said Customer & " " & Conveyor.

It could have been as simple as Customer & Conveyor, but I personnaly always insert a space in between the 2 fields.

Now, it surely depends about exacly how long this Concanation would be as FM has some limits in the indexing. Further more, Names could bring you to duplicates.

May be working with Ids would be more secure then

July 24, 200322 yr

Author

Sorry for the confusion, BUT i still don't understand how to carry htis KEY out.

I can see the purpose of connecting the 'customer' & 'conveyor' fields together but i don't understand how to do this.

Can you/anyone explain how this process works ?

Corey....

July 24, 200322 yr

Hi again,

You need 2 calculations, one for each file.

c_MainConcanate = Customer & " " & Conveyor

c_RelatedConcanate = RelCustomer & " " & RelConveyor

Now, create a relationship ConcanateRelationship using the c_MainConcanate for the left side and the c_RelatedConcanate for the right side of your relationship.

In the Main File, define your 3 other fields to be "lookup fields" using the ConcanateRelationship.

As soon as you will have filled the 2 main fields (Customer and Conveyor), FM will test for any valid relationship and auto-fill the 3 other fields.

If not, the fields would be set to blank and you'd fill them manually.

Hope it is clear now. I won't know how to explain it better, sorry.

July 24, 200322 yr

Author

When i enter Customer & " " & Conveyor into the customer calc. i get a error, saying it will create a circulation. ??

same for Conveyor calc.

any ideas ?

July 24, 200322 yr

You're dealing with three fields here: Customer, Conveyor, and the concatenated calculation of the two. Make sense?

July 24, 200322 yr

You'll probably laugh now (I hope)...

DB1:

customer=fred

conveyor=C100

length =100 Mtrs

width =1200 mm

thick =10 mm

Concanation (calc) = customer& " "& conveyor --> fred C100

DB2:

customer=fred

conveyor=C100

length = AutoFill -->100 Mtrs

width = AutoFill--->1 200mm

thick = AutoFill ---> 10mm

[color:"red"] Concanation (calc) = customer& " "& conveyor --> fred C100 [color:"brown"]

Relationship = DB2:Concanation::DB1:Concanation

July 25, 200322 yr

Author

THNX FOR THE REPLY AGAIN -)

I finally got it to work, SORT OF.

BUT the problem is IF i enter a Customer and Conveyor that matches in DB1 then the other fields are input, BUT;

IF there is a customer with the SAME name Conveyor (say C200) there are values that are input into the other fields BUT are NOT associated with the related Customer OR Conveyor.

EG.

Customer=fred

Conveyor=C200

1St instance works fine

Then i add a new record and puy it:

Customer=jack

Conveyor=C200

The other fields are filled in but with unrelated values from another customer:

SAY:

Customer=John

Conveyor=C400

Any idea's??

Corey....

July 25, 200322 yr

Author

THE DISHES ARE DONE

July 25, 200322 yr

Author

THE DISHES ARE DONE

I have solved the problem i had,

and YES now that i can SEE it with HIND-SIGHT i can LAUGH at my self.

I had the Calc. result set to give a NUMBER value, where it should have been a TEXT value.

Thnx for the help guy's.

Cheerio from the LAND DOWN UNDER

Corey....

July 25, 200322 yr

Laugh at yourself ?!

Rome wasn't built in one day.

July 25, 200322 yr

Author

Just a thought on the above calc.

Is there a way i can set the Calc. to Probit a Duplicate record.

So that there is NOT more than 1 record for say:

Customer=fred

Conveyor=C100

I tried to set the Calc. field to a TEXT field with a Calc. but it would not return the input values for the other fields.

I want to prevent the option of having 2 x fred;C100's ECT.

July 25, 200322 yr

Author

Sorry had another thought also.

If there was NO match in DB1 from what is entered into the Customer and Conveyor fields in DB2, is there a way of having a NEW record created in DB1 when it is put into DB2 ?

Also i noticed that if there is NO match in DB1 with the Customer & Conveyor fields in Db2, i CANNOT place values into the other fields in DB2.

How can i be allowed to do this ?

July 27, 200322 yr

I thinl you are using related fields while we suggested lookup fields.

As the relationship isn't valid, you cannot fill any data in these "not related fields".

The only way you could create a record would be by script. As a matter of fact, there is an example posted in the Sample Forum, just some days ago by Chopper. You'd find 2 opposite methods to do this.

July 28, 200322 yr

Author

The other fields that require manually entered values if no matches are found are ALL LOOKED up fields.

I tried to define the relationship to 'ALLOW CREATION OF RELATED RECORDS' but i then get an error when i enter a value into the field.

Corey....

July 28, 200322 yr

Hi,

Not sure what is a Lookup field for you, or which relationship you're using.

Here's attached a little example.

HTH

Corey.zip

July 28, 200322 yr

Author

hi,

thnx BUT i cant open any thing above Version 4.

I have the OTHER fields (not customer & conveyor) as either a TEXT or NUMBER field.

Under Options i have set them to LOOKUP their value from the related DB (DB1) field named the same. IE Width= :Width.

The relation ship between the 2 DB's is fine BUT when it comes to entering a CUSTOMER and CONVEYOR that does NOT match any in the DB1 fields, then i need to enter MANUALLY the values into DB2 fields- width,thick,length ECT.

At the moment i can click on the fields, BUT when i try to anter a value i get an error.

What i would like is If there is NO match to the CUSTOMER & CONVEYOR in DB1 from a new record in DB2, then a NEW RECORD is AUTO CREATED in DB1 as i fill in the values MANUALLY for the other fileds in DB2.

The only RELATIONSHIP i have is the one you helped me with earlier.

It ties the CUSTOMER and CONVEYOR fields together. It works SPOT ON.

BUt when NO match is present in DB1 i need to enter at LEAST the values MANUALLY into DB2.

Thnx for the assistance so far , and please bear with my ignorance with some of FMP solutions.

Looking 4ward to any solution you/anyone may have ?

July 28, 200322 yr

Corey,

You cannot set the related record through the Customer ID (nor from the Conveyor), because it is the Foreign key here, meaning you would end to a "circular" relationship.

What you're into would be done by a script though, involving global fields. I even would do it at first using global fields for all new entries in the Db2 file.

You'd need

- g_customer (global, same format than customer, you may attach a Value List of Customers to this field)*

- g_conveyor (global, same format than conveyor, you may attach a Value List of Conveyors to this field)*

- a concanation of these 2 global fields - gc_Concanation

- a field c_constant1 (indexed, calculation, num result, equal to 1) in Db1

- a field c_constant2 (indexed, calculation, num result, equal to 1) in Db2

- a Constant relationship (call it Constant2To1 in Db1 :

Db1:c_constant::Db2:c_constant.

- a GlobalConcanateRelationship Db2:gc_Concanate::Db1:Concanate

- a Global Customer Relationship Db2:g_customer::Customer :Customer

- a Global Conveyor Relationship Db2:g_Conveyor::ConveyorFile ? :Conveyor

Trigger this basic script :

If (IsEmpty (g_Customerfield) or IsEmpty(g_Conveyor)

..Show Message (Please Fill the fields)

..Stop Script

End If

If (Is Valid(GlobalConcanateRelationship::Record_Id))

Set Field (Customer, g_Concanate)

Set Field (Conveyor, g_Conveyor)

Else

Perform External Script <Create New records in Db1>

Refresh

Go to Field Num

End If

where the External script is

New Record

Set Field (Customer,::Constant:g_customer)

Set Field (Conveyor, ::Constant:g_Conveyor)

If you adopt the VL to both fields, I suggest you use a conditional Value List (think it is possible in FM4), so that once you've entered the Customer Id, you'd be listed with only the Conveyor matching this Customer.

If one or both of your fields are empty because there is no customer or conveyor for the names/codes you entered, then trigger separate scripts for each one in their respective files with same principal of the <Create New Records in Db1>

HTH