Our Preferred 5 Star Hosting Provider - Award Winning FileMaker Pro Hosting
Results 1 to 5 of 5
  1. #1
    Forum Member
    Join Date
    Jun 2017
    Posts
    3
    Location
    CA US
    FileMaker Pro Software
    FileMaker 14 Advanced
    OS
    Windows & Mac
    Skill Level
    Novice
    Last Post
    06-16-2017
    Local Date
    10-20-2017
    Local Time
    02:30 AM
    Last Active
    07-03-2017

    Keep Data After Relationship Change?

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

  2. #2
    Senior Moderator - FMT Preferred Developer Consultant - Netherlands, Europe, Asia, USA Maarten Witberg's Avatar
    Join Date
    Mar 2004
    Posts
    6,135
    Location
    netherlands
    FileMaker Pro Software
    version 11, 12, 13, 14, 15
    OS
    Windows & Mac
    Skill Level
    Developer
    Last Post
    06-29-2017
    Local Date
    10-20-2017
    Local Time
    12:30 PM
    Last Active
    07-16-2017
    Not sure if I follow all that, can you post a screenshot of the relationship graph?
    Stick to what you don't know.

    http://www.maartenwitberg.nl

    Filemaker 15 Certified Developer

    -------

  3. #3
    Forum Member
    Join Date
    Jun 2017
    Posts
    3
    Location
    CA US
    FileMaker Pro Software
    FileMaker 14 Advanced
    OS
    Windows & Mac
    Skill Level
    Novice
    Last Post
    06-16-2017
    Local Date
    10-20-2017
    Local Time
    02:30 AM
    Last Active
    07-03-2017
    Hello Maarten,

    Thanks for the response! After looking at your reply (especially, 'relationship graph'), I think the solution jumped out of my brain. The way the system is currently set up is a one-to-one relationship between Equipment and their Location (I did it this way, thinking that a piece of equipment can only be at one location at a time, therefore, one-to-one seemed correct). However, in reality, equipment moves locations over time, so I decided to create a third (intermediary) table (EquipmentLocation), and utilize one-to-many relationships between Equipment and EquipmentLocation, and between EquipmentLocation and Location. This introduces a minor issue with value lists (enabling ANY equipment that has passed through a location to be available in a list, versus only equipment currently at a particular location), but I will find a way to sort it out (hopefully).

  4. #4
    Senior Moderator - FMT Preferred Developer Consultant - Netherlands, Europe, Asia, USA Maarten Witberg's Avatar
    Join Date
    Mar 2004
    Posts
    6,135
    Location
    netherlands
    FileMaker Pro Software
    version 11, 12, 13, 14, 15
    OS
    Windows & Mac
    Skill Level
    Developer
    Last Post
    06-29-2017
    Local Date
    10-20-2017
    Local Time
    12:30 PM
    Last Active
    07-16-2017
    Good to hear

    but I will find a way to sort it out (hopefully)
    If you need help we're here ;-)
    Stick to what you don't know.

    http://www.maartenwitberg.nl

    Filemaker 15 Certified Developer

    -------

  5. #5
    Forum Member
    Join Date
    Jun 2017
    Posts
    3
    Location
    CA US
    FileMaker Pro Software
    FileMaker 14 Advanced
    OS
    Windows & Mac
    Skill Level
    Novice
    Last Post
    06-16-2017
    Local Date
    10-20-2017
    Local Time
    02:30 AM
    Last Active
    07-03-2017
    Quote Originally Posted by Maarten Witberg View Post
    Good to hear


    If you need help we're here ;-)
    Thanks! I appreciate it. The more I try to do this the 'correct' way (from the old 'flat file' to the proper 'relational'), I'm coming up with good ideas... but the ideas are more complicated to implement than expected, hahaha.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Creat records automatically through relationship after status change in related table
    By Jillegolas in forum FileMaker Pro 12 - All versions
    Replies: 2
    Last Post: 06-21-2012, 12:29 PM
  2. Script to change Data Source for a Table
    By ccbran in forum FileMaker Pro 12 - All versions
    Replies: 3
    Last Post: 05-31-2012, 12:56 AM
  3. change data from columns to rows
    By osric in forum FileMaker Pro 10 Advanced
    Replies: 1
    Last Post: 11-10-2011, 02:15 PM
  4. Slected data get change in pop-menu automatically
    By devil in forum FileMaker Pro 8.5
    Replies: 3
    Last Post: 11-04-2009, 01:35 AM
  5. Can I change field data within a CF
    By AlanBarker in forum FileMaker Pro Custom Functions
    Replies: 1
    Last Post: 03-11-2008, 08:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Welcome to the Original FileMaker Forum
With designated forums for almost every FileMaker topic...FileMaker Today is a FREE to join community where you can boost your FileMaker expertise, build better apps & solutions and interact with over 42,000+ FileMaker Developers from all over the world. If you're building FileMaker solutions, this is the place for you.
Follow FMT
We Are FMT
FMT is home of The World's leading FileMaker News Site, Worldwide Developer Directory, FileMaker Connect Community and so much more.