Using Data from many tables. Relationships are killing me

[jeddell]

Hi all,

I've been getting more an more frustrated with the way Filemaker works. I come from and MS Access background and I'm really struggling with Filemaker. I want to learn this, so please forgive me if I dont just hand over my database for you to look at. I did this and someone dazzled me with there brilliance and I ended up deleting the database because it was just way over my head and I could not replicate anything. I am only very new to this program, but I do understand the basics of relational databases

I have a project concept that I;m working on of which I have done many similar projects using MS Access. Part of this is what I'm working on now, but I'm just not getting it. The image below shows four tables, Units, Technical inspections, Craft Type and Craft. The relationships shown in the image are correct and they will all be a one to many relationship. to explain the tables: 

CraftType_tbl: This details the type of watercraft, its name and the capabilities of this fleet of craft, sea state, Capacities. This data would apply to all the craft within the fleet

Craft_tbl: This is data specific to each craft (please forgive any duplication here as I have been trying to get things to work to no avail). The Craft registered number, any serial numbers of equipment on board, who owns the craft etc

Units: This lists units that own watercraft and any information specific to this unit

TechnicalInspections: This is data which gathers information from all of t he tables above as well as some data of its own to build a unique report on the particular craft from the Craft_tbl. These reports are conducted every year on each craft in the craft list. You could call this the "Join table" in this instance.

THE PROBLEM

I want to create a Technical inspection front page (layout or whatever you want to call it) using the data from the three forms. I DONT KNOW if i have to transfer the data into the Tech inspection table or if the relationship carries data into the tech inspection........I'm completely lost here. I started to create a layout using this relationship, but no matter what I did, I could net get any data already in the craft type table and craft table to come across into the Technical Inspection layout.

WHAT I'm looking for:

Someone who is willing to be patient with me and step me through this process so I can learn from you. I have bought a number of tutorials and watch countless hours of you tube, but they are glossing over things that I just don't get.

THE AIM
To create a Technical inspection with the data already in the database and only having to add the data I need unique to the Technical Inspections table. Eventually this will be printed out into a PDF File on an A4 Page.

I hope there are poepl here that are willing to work with me.

Kind regards

Dean

[Wim Decorte]

The biggest misconception here is that you think the relationship graph is an ERD.  It is not.  So this statement, based on your graph:

The relationships shown in the image are correct and they will all be a one to many relationships

Is not possible.  Each relationship on the graph is bi-directional so not all relationships can be one-to-many in all directions.

Each object on the graph does not represent a table, but a table occurrence.  Before you get to deep into other thinking, read up on this notion and the various graph management approaches.

This is a good place to start:

http://www.nightwingenterprises.com/Resources/approaches_to_graph_modeling_en.pdf

Anchor/Buoy is an easy one to grasp and will get you where you want to be quickly: http://www.kevinfrank.com/anchor-buoy.html, but keep an open mind for the other approaches.

 

As to your problem, context is everything.  You will need a layout that is based on the TechnicalInspections table occurrence.

THE PROBLEM

I want to create a Technical inspection front page (layout or whatever you want to call it) using the data from the three forms. I DONT KNOW if i have to transfer the data into the Tech inspection table or if the relationship carries data into the tech inspection.

If the relationships are set up correctly you can get data from that context you will be able to see the related data.

Whether you actually need to bring some of that data over into the inspections table has nothing to do with relational design, but is a business logic question: if you want each inspection to reflect the state of the craft and its ownership at the time of the inspection then you should not use the most actual related data since ownership may change over time.  In that case you will need to grab and store the point-in-time data.

 

What you probably need is to find a tutor/mentor in your area, someone who can sit with you for an hour or two and walk you through the basics.  You can start that search here: http://developer.filemaker.com/search/

If you have not already done so, purchase the FileMaker Training Series (http://www.filemaker.com/learning/training/fts.html - only $50 or so) and go through the exercises.  Don't skip any because while you know relational design based on Access, you need to learn the basics of how it is implemented in FM.  If you skip it you'll keep getting blocked on conceptual things.

 

 

 

 

 

[jeddell]

Thanks for the Links, I'll wade through them an see how I go.

Cheers

[jeddell]

OK, Going through t he Anchor Bouy resource. I structured the RG to what the presentation is saying. Is this the attached image the concept, with the Base table being the table used in my Layout? I ask this as the actual relationships haven't changed. I've added another instance of the Craft Table in the hope of establishing a method of pulling in data relating to the craft type table.

[Wim Decorte]

That looks like proper A/B.  So what is not working?

[Jonathan Hogle]

Jeddell,

I have a few recommendations for you:

1.) If you haven't already, make sure your ID'S and you fkID'S are the EXACT same. Relationships are very Specific. 

 

2.) My company, Richard Carlton Consulting. Inc. (RCC), offers one on one training with skilled FileMaker engineers who know how to help beginners learn more advanced topics in a simplistic way. For additional info on that, email: [email protected]

 

3.) RCC also offers a Paid ($40) FileMaker Pro 13 training course that has helped many individuals such as yourself learn FileMaker and troubleshoot/create their own databases. You can download the course here: http://learningfilemaker.com/FM13Pro-Training/

Inside that course, the videos we have that would help you are as follows:

#1001 - What Is A Relationship

#1003 - Relationship Graphs & ERDs

#1030-1031 - Anchor Buoy Methodology Parts 1-2

#1004 - What Is A Portal....And One To Many Relationships 

These are the ones that I believe would help your case specifically, but we have an entire "Relationships" Section as well as many other topics covered.

 

Thanks!

- Jonathan Hogle

 

 

[jeddell]

That looks like proper A/B.  So what is not working?

Haven't got that far yet.....baby steps. Just try to understand the structure before I start asking dumb questions that I could have answered myself with a little learning. 

Jonathan, Thanks for the info. I'll look at the training material ite you sent.  and yes the ID Keys is always set as the first field in all databases I create in <S Access so I am aware of  this requirement. What im trying get my head around at the moment is how filemaker manages these relations.

Cheers all

Edited September 8, 2015 by jeddell

[ggt667]

If you do that one, and import some data into the tables, I'd suggest you start by making a layout based on Craft_tbl make sure you have some field with recorgnizable from each table on the layout and then start clicking on the next and previous record button until you see what is going on your new layout until you see what is going on.

Edited September 9, 2015 by ggt667

[jeddell]

If you do that one, and import some data into the tables, I'd suggest you start by making a layout based on Craft_tbl make sure you have some field with recorgnizable from each table on the layout and then start clicking on the next and previous record button until you see what is going on your new layout until you see what is going on.

Yes, I do have a layout for craft_TBL and for Craft Type Table as well. Each of the layouts will have there own specific roles to play in this solution. What I am working on is the Technical inspections. I started a layout for this, not much design work yet, I wanted to get functionality working first. the technical inspection will be a new record which as Wim post is a snapshot of the data, at the time of creating the record.

Much of t he data that goes into the Technical inspection Layout is held in the Craft Type Table and then the craft TBL table.

Mu concept was to  set a drop-down on the CraftIDFK to select the craft Type. Once that was selected, automatically fill in the related data from the craft Type Table into the Technical inspections by using the Foreign key in the CraftTypeFK field. From there, I wanted to be able to select from the Craft ARN field as a drop own filtering that ist vied the Craft Type FK and then populating  the rest of the fields in the Technical inspection from that table.

Sounds a little complex, but basically I want to automatically add the data into a new record in  the Technical inspections layout using data from the Craft Type Table and the Craft Table. and then add the rest of the data manually as that would then be unique to the Technical inspection. Listening to Jonathons video tutorials, I'm guessing this will be a script, but he didn't cover inserting data into a field from the related in the child TO.

Edited September 10, 2015 by jeddell

[ggt667]

but basically I want to automatically add the data into a new record in  the Technical inspections layout using data from the Craft Type Table and the Craft Table.

There are 2 ways that I come to think of that you can do this

1) Valuelist( https://www.youtube.com/watch?v=v5IaCEt2PmM ) based on FK in combination with a relationship to the value you'd like to show in which will keep normalization good.

2) Lookup( https://www.youtube.com/watch?v=9J-ZKZ9HPww ) in which will harvest redundant data, but may be good for keeping track of history in some cases.

Edited September 10, 2015 by ggt667

[jeddell]

gg,

It appears as though I stumbled on the "lookup" method, but didn't get it because I was missing some data in the craft Type records. Look up is the answer for this situation. I can progress this further now. My next challenge is to filter a second drop down list (Craft TBL) based on the selection of the first drop down list (CraftType_TBL). I have seen some tutorials on this, but this was where the RG was starting to give me a headache. I'll give it another go with the A/B RG layout and see if it makes sense.

Thanks for the advice

OK, So I've managed to get the second popup list to select a number of ARNS based on the craft type selected in the First Popup list. The problem is, I cant select anything except for the first record. I have no idea why. I followed this tutorial....

(https://www.youtube.com/watch?v=nVTRGhIEz6E)

The other issue i'm finding is the Lookup function does not work all of the time. I set it us using the calulated field and the Additional Fields for Ctraft Type work fine, but the CraftTBL fields worked once but never worked again. I had unchecked the "Do not replace value in field" so It would change. The settings are the same as for the first field but they wont work.

Edited September 11, 2015 by jeddell
Update after testing methods

[ggt667]

You probably do not want lookups, I'd go with merge field showing the related value, unless you really need the string copied( lookup ) into your current record.

If you are to run your solution over WAN eventually use merge field in your solution when the user is not allowed to enter data.

Edited September 12, 2015 by ggt667

[Wim Decorte]

Even if you do need the data statically stored as "point in time" I agree: I would not use lookups.  I favor scripts to take and set the data as part of an overall workflow, not buried at the field definition level.  That way you have more control and will not accidentally trigger a re-lookup.

[jeddell]

OK, Thanks guys

Wim, Yes I did use the script method rather than the lookup. From what I hear its meant to be a little more efficient anyway.

OK, So I made some progress this weekend, although I think a lot of it was just blind luck. This is related to the selecting and setting of the Craft Registered number AFTER I have selected the Craft Type. I struggled with this for a while and I changed the relationship in the RG and it all worked. Unfortunately, I'm not real sure what I did so differently to make it work. I know what I did physically, but don't really understand why its now working. I'll put a YouTube video together to show my progress I think because its too lengthy to try and write all of this. If you would rather me write, then just let me know.

My latest quandary is the rezising of my Layouts. This solution will have a significant amount of layouts and each does a certain task. None of them have enogh data to fit an entire desktop screen. I hate empty real estate in a database so I was going for a "popup" arrangement for the layouts. What I cant seem to figure out is how do I get the Layout window to size automatically to the layout size?

[jeddell]

Hey Guys,

Here's my Video to you as I stated earlier. Topic is Automatically re sizing Layout windows to fit the Layouts. Hope you get a better understanding. Comments on the video welcomed

(https://www.youtube.com/watch?v=F-21AuBGhu0&feature=youtu.be)

 

Edited September 13, 2015 by jeddell
Video ink was wrong

[Wim Decorte]

What I cant seem to figure out is how do I get the Layout window to size automatically to the layout size?

Script step: Adjust Window > Resize to Fit option

[jeddell]

Awesome! Can crack on now. Will check back when I get stuck again...Thanks heaps

[jeddell]

New Question:

I have a field in a portal detailing hours (ERT). I have a Summary Fields in the table that totals the ERT. What I would like to do is total only the hours for the tasks that have not yet been competed or have not been moved to the next slipping. I'm guessing this will need to be a Calculated field rather than a summary field, but I don't know enough about the syntax yet. So,

1. How to do the calculation, and

2. Is there somewhere that I can go to understand how the syntax works?

Cheers

Dean

[Wim Decorte]

Careful with calculations and summary fields.  They are easy to use but could end up bogging down your solution as it grows.

You can also think "workflow": if a task gets completed a script is run that flags the task as complete and updates the total hours.  That would leave the total hours as a static non-calculated number field that is fast to search on.  Whereas if you make it a summary / unstored calc and you ever want to search for say "jobs that took more than 5h" then your search would trigger all those calculations over and over again to produce the result.