Jump to content
The ORIGINAL FileMaker Community - Forum - Online Business Apps & Software Forum

Recommended Posts

vwgtiturbo77

Hello, I have a 'data retention' issue that has been driving me absolutely insane over the past two days. It is actually a multi-part issue, but rectifying the issue in this post may solve the others (so I'll keep it simple... for now).

 

In an nutshell, I have 3 tables related by a locationID.

 

Location

locationID

 

Equipment

equipmentID

serialnumber

fk_locationID

 

FLIGHT

flightID

fk_locationID

fk_equipmentID

 

The FLIGHT table is the main data storage entity; Location and Equipment tables just provide data to fill FLIGHT table.

 

While Equipment stores the current location of a piece of equipment, FLIGHT also stores the location of the current flight (the flight's origin).

 

There is an Equipment value list where I choose the equipment used on this particular flight, that consists of Equipment::equipmentID (first field of value list) and Equipment::serialnumber (second field of value list), with only the 2nd field (serialnumber) being visible. This value list only shows the equipment that is related by the Location table (locationID).

 

Relating FLIGHT and Equipment via location allows me to select from the equipment that is actually on-site (i.e. if I am in CA, the value list will only show Equipment that is also in CA, versus listing all of the Equipment, to include those pieces at overseas locations).

 

The problem comes when the relationship is broken (e.g. in the future, due to Equipment movement, I will go into the Equipment table and change the fk_locationID to reflect the Equipment's new location).

 

After this occurs, pop-up menus show only the record number of the equipment, instead of the serial number that I have set up in the value list.

 

Researching this, I did find a 'solution' that entailed creating a second value list that didn't use Equipment::equipmentID (it only used one field, using Equipment::serialnumber), and using a FLIGHT field that I renamed to 'equipmentserialnumber' (since it now stores the serialnumber instead of equipmentID). To make the location realationship work, it was necessary to relate FLIGHT::serialnumber to Equipment::serialnumber, which creates a second Equipment occurrence (FlightEquipment, for example).

 

The problem I am having (and it is likely a rookie mistake, admittedly) is that, for other (more in-depth) areas of my database, I am having a helluva time relating other tables to the Equipment table through the Flight table (to gather only information about equipment used for a particular flight, for example). I'm nearly convinced that it is due to the serialnumber/location relationship workaround, as these items weren't an issue prior.

 

Do I have any other options to keep the Equipment::serialnumber visible in FLIGHT, after the relationship is no longer valid? I've spent two days wracking my brain on this, and being wet behind the ears on DB design, it's driving me insane (for reference, this project is the result of watching many DB design lectures, and attempting to convert an old flat-file Filemaker database to a 'real' relational database. The original intent was to use Postgres, however, I am not the only person that will be using this, and I don't have the knowledge necessary to ensure that it would be successful).

 

Thanks in advance for any insight! I apologize in advance if the explanation is unclear; sometimes it would be easier to simply post a file so others can see what exactly is going on, but with sensitive information included, that isn't always an option).

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use