Multiple relationship question(?)

[girish]

Hi there folks - I'm hoping this is an easy one, and I'm sure it must have been asked before but I'm buggered if I can find the answer.

I have two databases - hardware.fp5 & users.fp5. "hardware" contains information about the computers in my section and has the following fields :

ip (= ip number)

compname (= computer name)

dnsname

serial

"users" contains information about the users in my section and has these fields :

fname

sname

id

comment

(Computers can have more than one user, and users can have more than one computer).

I can successfully create a relationship between the two files based on the compname field so that if I enter an existing compname in a new record in user.fp5 it both brings across the correct computer details from hardware.fp5, and adds the correct user details into hardware.fp5.

* OR *

I can successfully create a relationship based on the ip field, so that if I enter an existing ip in a new record in user.fp5 I get the appropriate computer information from hardware.fp5 etc.

However, if I use the compname relationship I can't use the ip relationship and vice-versa.

What I'd like to be able to do is have the option to enter either an ip number or computer name in a new record in user.fp5 and have it grab the correct information from hardware.fp5. So - if I enter a compname in user.fp5 all computer information (including ip number) should be displayed from hardware.fp5, and if I enter an ip in user.fp5, all computer information (including compname) should be displayed. Does this make sense?

Can anyone tell me how to do this (or point me to an existing solution)?

Thanks

Girish..

[ESpringer]

Your relationship needs to pivot on *one* matching field for each end. (For more complex purposes, you might set up multiple relations, but not for basic matching and tracking who has what.) Still, after deciding which field to use, you can mimic the effect of being able to enter in either one.

If in fact all of the hardware uses an IP number (you're not tracking keyboards and monitors separately, for example), then it would be a fine key for a relationship. Otherwise, you might assign your own unique IDs to the hardware, using auto-enter serial numbers (I'd recommend this for expandability, and because IP numbers tend to run in batches that start out all the same.) ... Anyway Suppose you decide on IP address as the best key field, for now, and modify this to suit if you choose another unique identifier...

So, here's the data entry trick: set up a calculated concatenation field in the hardware db that would go like this:

cConcat: ip&"

[Fenton]

"(Computers can have more than one user, and users can have more than one computer)."

This is a classic "many-to-many" situation. It calls for 3 files. The actual assignment of hardware all happens in the central "join" file.

It has the ID fields from both Hardware and Users, as separate fields. A record is a unique entry of the combination of hardware and user, and date(s); because someone may use an item, return it so that someone else uses it, then get it again.

There are relationships from each of the main files, based the respective primary ID. The join records show in a portal on the layout in the main file. You can turn on "allow creation of related records," so you can enter new hardware for a person by typing (or better, choosing from a drop-down list of hardware) in the portal.

There would also be a portal in hardware to the join file, so you could see one or multiple users of an item. It would also serve as a kind of history for the item.

This is how I'd do it: The relationships to the join file would be sorted descending by date. They would not have "allow creation on." A button would run a script in the join file to create a new record. There would be fields in that file to auto-enter date and time. The relationship would be sorted by those. This causes the newly created record to appear at the top of the portal.

The rows show most recent first. That way you don't have to worry if the portal's got enough rows.

Attached is a similar setup. It's for computer repairs. So you'd have Users instead of Repairs. It only illustrates the technique from the equipment side, but it's the same thing.

Portal Both.zip

[ESpringer]

Indeed, apologies for not attending to the many-to-many aspect; my response would have made sense only in a situation where each user has one item, or multiple items falling into distinct categories. Tip o' the hat to Fenton.

[girish]

Hi guys - thanks very much for these suggestions/instructions. Unfortunately, I don't think I've quite understood it just yet.

I take your point, Fenton, about it being a many-to-many situation and I was thinking about using 3 files (hardware, users, summary). I also had a look at the attached examples (thanks!), but can't quite get them to do what I want (expect?). For example, in the ComputerRepairs file I'd like to be able to add a RepairID and have the RepairName automatically complete, OR add a RepairName and have the RepairID complete. At present, adding a RepairID works OK, but adding a RepairName doesn't complete the RepairID.

I get the feeling that my solution lies somewhere in combining ESpringer's tip about using a calculated concatenation field (nice tip, BTW!) with Fenton's points about a many-to-many relationship.

I can follow ESpringer OK up to the creation of a global text field in users, but sort of lose it after that - sorry :-[

"(Then, create a relation between this new global in Users and any records that match it in cConcat in Hardware)" - does this mean create a new relationship between the gHardwareKey field (in users) and the cConcat field (in hardware)?

"(Put the global on a layout in Users, and next to it add in, as a Related Field, the RELATED IP address from the Hardware database)" - at this stage, I think I've got two relationships : 1= ipnumber to ipnumber, and 2 = gHardwareKey to cConcat. Should I use the IP address from relationship 1 or 2?

Sorry about the dumbass questions, but I get the feeling that I'm misunderstanding a fundamental point about relationships here!

I'll attach a couple of basic draft files here to show you how far I've got.

Girish..

files.zip

[dbruggmann]

Hi Girish

First to your last questions:

Point one:

[dbruggmann]

Here's the attachement with the files (I couldn't post them together with the post above without loosing all the text formatting, so i had to split it up).

filesnew.zip

[girish]

Hmmm - I think I may be talking at cross purposes here, or else I need to re-think the way I think about relationships!

I really appreciate the suggestions and sample files - I'm going to play around with them a bit to see if I can get my head around it. One question, though :

**

[dbruggmann]

girish said:

So, for example - in the ComputerRepairs file, if I create a new record, put in a InvComputerID of I001 and a RepairID of R003, I get the following " I001 Performa 475 R003 Fan".

However, if I if I create a new record, put in a InvComputerID of I001 and a RepairName of Fan, I get the following " I001 Performa 475 <Blank> Fan" - i.e. the fan field isn't automatically updated.

Now, if I try to change the RepairID field to use a lookup using a new relationship as suggested, I get the following error : "The lookup uses the field "RepairID" in its definition. Choose another field".

I can create a second RepairID field (e.g. RepairID_2), and that works OK, but that means I would have use one RepairID field when searching by name and another when searching by ID.

You're right. That's actually how I did it in my copy of Fenton's files: I duplicated his field RepairID (and renamed the original to RepairIDwithoutReference), defined the new RepairID as a lookup field and adapted afterwards the layout and relationships to the new field. Then the lookup worked both ways.

I did it this way, just because I didn't want to