Relationship between DB's depending on 2 Field's

[corey]

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.

[Ugo DI LUCA]

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.

[corey]

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....

[Ugo DI LUCA]

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

[corey]

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....

[Ugo DI LUCA]

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.

[corey]

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 ?

[-Queue-]

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

[Ugo DI LUCA]

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

[corey]

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....

[corey]

THE DISHES ARE DONE

[corey]

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....

[Ugo DI LUCA]

Laugh at yourself ?!

Rome wasn't built in one day.

[corey]

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.

[corey]

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 ?

[Ugo DI LUCA]

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.

[corey]

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....

[Ugo DI LUCA]

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

[corey]

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 ?

[Ugo DI LUCA]

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

[corey]

WOW,

I can see why in your user PIC uyou are sitting in a F1, i am trying to keep up with your V12 TWIN TURBO FERRARI in my 100cc CLUBMAN go kart. -)

That last post went straingth over my head.

I have created 2 DBV samples of what i have and to-date i have managed to get in DB2 the ability to enter MANUALLY the other fields.

But i can't have it 'WHEN THERE IS NO MATCH OF CUSTOMER & CONVEYOR IN DB1',

AUTO create a NEW record in DB1 to MATCH the New one in DB2.

maybe you can look at the example i have

DB1.zip

[Ugo DI LUCA]

See attached. FM4 format using your settings and accomodating the global fields trick.

Now, I'm still curious if a new customer added shouldn't be created in the Customer file as well. Do you have Customer and Conveyor File ?

You should use Ids instead of names...

HTH

corey2.zip