1 2 Previous Next 18 Replies Latest reply on Feb 27, 2017 9:36 PM by petey

    Related Data Not Showing

    petey

      Hey!

       

      GOAL

      Show related data in layout (simple, right!?)

       

      DATA

      Table: T34_School_Dates

      Desc: Layout with table data, e.g scheduled school dates, their location, cost, etc.

       

      Table: T35_School_Day

      Desc: Layout with table data for that school day, e.g. how many students are signed up, what level they are, (will also have what happened that day, etc.

       

      What I did:

       

      1. I created a relationship (presumable correct). Staring with the primary key for T34_School_Dates (ID_School_Dates)

      2. I created a foreign key in T35_School_Day: id_school_dates

      3. Linked them up, and checked "allow creation of records....."

      4. Pulled into my School Day layout, the field "Date1" from the related table.

       

      Doesn't show data.

       

      I'm curious if the keys were setup correctly?

       

      When it wasn't working, I changed them back and forth between text and number. Here's what they are currently

       

      PK: ID_School_Dates - text, Auto-enter Serial, Indexing (minimal)

      FK: id_school_dates - text, Indexed (all)

       

      Screenshots:

       

      Screen Shot 2017-02-25 at 8.24.28 PM.png

      Shows no data in browse mode. Field is set to display in browse mode.

       

       

      Screen Shot 2017-02-25 at 8.25.00 PM.png

      Relationship setup. Is it right?

       

      Thanks,

       

      Pete

        • 1. Re: Related Data Not Showing
          philmodjunk

          Your relationship would link one record in T35b to possibly many records in T35.

           

          Thus, placing Date1 from T35b on a layout based on T35 will show either data from one record or no records depending on the values in your two match fields.

           

          So your relationship may or may not be correct as I don't know if you want a 1 to many relationship from T35b to T35.

           

          Date1 could be empty or the value in T35b::id_school_dates might not match to any record in T35. So your relationship could be correct but the field Date1 could be empty or the data needed to match the records might not be there.

          1 of 1 people found this helpful
          • 2. Re: Related Data Not Showing
            petey

            Okay, that's helping me better understand how it works.

             

            How the fields relate is the part I get confused about, because logically, how would the record pulling data know what record to pull it from?

             

            I just read about the different types of relationships, and it kind of makes sense, but I need to see an example to fully have it.

             

            This might help sort out what kind it is:

             

            Table: School Dates

            Field: Date1

             

            There are 61 records in School Dates. Each record has Date1. So that would be one field with multiple rows in the table.

             

            Table: School Days

            Field Date1 (from School Dates)

             

            I'm looking to create 61 records in School Days with several related fields, that match each of the records from School Dates.

             

            Screen Shot 2017-02-25 at 10.01.22 PM.png

            Screen Shot 2017-02-25 at 10.04.56 PM.png

             

            I hope this helps. Is this a many to many? One to many?

             

            Cheers!

            • 3. Re: Related Data Not Showing
              petey

              Wait...is this a joined table?

              • 4. Re: Related Data Not Showing
                BruceRobertson

                Your choice of terminology is EXTREMELY confusing.

                School days.

                School dates.

                Something is one to many, or a join table, or something, but even though this is YOUR data and your concept; you don't know?

                WHAT?

                What does all this represent?

                • 5. Re: Related Data Not Showing
                  BruceRobertson

                  Also, FWIW, for foreign keys, it might be a tiny bit clearer to include the "f"

                  FK: IDf_school_dates - text, Indexed (all)

                  • 6. Re: Related Data Not Showing
                    petey

                    Bruce,

                     

                    I completely understand your consideration. Not being familiar with our company and the terminology, it would be confusing to an outsider.

                     

                    "School Dates" really means "Classes," or "schedule."

                     

                    "School Days" represents the actual day at the track and the details of that day. For example, what students are there, their level, if the turned in paper work. This table also includes lots of record keeping for what occurred on that day. Did anyone crash? What was the weather like, did everyone complete, any future service notes, etc.

                     

                    These are the terms they use, and have been for the last 38 years.

                     

                    I thought my descriptions in previous posts would make it clear what I was trying to achieve. Reading my last post, I spoke using current pop-culture. I can see how it would be mis-interpreted and made confusing. I tried to edit it, but couldn't find the option and frankly, I'd rather spend time in FM.

                     

                    I wasn't asking the community to tell me what my data is. I was asking if what I was trying to do, fits the description of a joined table...then I'd go down that route. I'm learning all this as I go.

                     

                    Got it on the "F" and "P." I'm using FM Starting Point by RC Consulting and that's what they do. I agree, it would be easier, but I want to keep everything the same.

                    • 7. Re: Related Data Not Showing
                      fmpdude

                      Bruce makes a good point. It's just good documentation, and a standard to indicate in the "related" table the table the key came from -- as part of the field name.  So, if you have an ORDERS table you would include the CUSTOMER PK in the ORDERS table so you would know which customer was for which order. So using a field like ORD_CUSFK, or ORD_CUSID, would make that field's meaning clear (as is the standard) to any DB person. Notice that in the CUSTOMER table, each field starts with "CUS" so the foreign key portion for the CUSTOMER PK in the ORDERS table "ORD_CUSFK" clearly documents that.

                       

                      Not giving you a hard time, but I also agree with BruceRobertson's other point: Having clear table names that are "self-documenting" (as much as possible) will help immensely with maintenance and having other developers not necessarily need to know all the "inside baseball" just to understand the data model.

                       

                      I think you know this already, but deciding the cardinality of a relationship between two tables is really only asking yourself if "one of this" (in one table) can have "many" in the child table. Of course, it's a bit more involved since you could have zero, 1, or many, too, in the child table, but it's more or less the same thing -- depending on business rules where you could have a CUSTOMER, say, but no ORDERS yet. The cardinality is still 1:M, but in an ERD, you would model that as 1 to "zero, one, or many".

                       

                      HOPE THIS HELPS.

                      • 8. Re: Related Data Not Showing
                        petey

                        Thanks fmpdude.

                         

                        Point taken. In future projects, I'll change the anchor-buoy naming convention. It's not worth changing the whole project at this point. Most likely the future developer will be me, or the company who created the base system.

                         

                        I guess I could see that point too. Externally they can call it whatever they want...even on the layouts. But for development sake, the table names should be "self-documenting."

                         

                        I kind of knew that...still learning. So when other describe it in a different way, it helps cement the idea.

                         

                        I think my original question really never got answered. Some of the replies got me thinking about how to structure the tables and relationships. I'm also going to get some professional coaching, so I'm sure I'll figure it out.

                         

                        Will post on here what the solution was...to help others.

                         

                        Cheers!

                        • 9. Re: Related Data Not Showing
                          BruceRobertson

                          Probably don't have any classes enrollments in your wayback machine.

                          Me at S.I.R. Kent WA about 1978 on my Ducati 860GT.

                          bfr_134_SIR_2b.jpg

                          • 10. Re: Related Data Not Showing
                            jfletch

                            Wow, the license plates in CA were a LOT nicer at that time!

                            • 11. Re: Related Data Not Showing
                              petey

                              Wow! That is classic shot.

                               

                              That track is in my top 3, most dangerous tracks to ride.

                              • 12. Re: Related Data Not Showing
                                BruceRobertson

                                Crashed in turn 7, broke only my left little finger. Figured that was enough, just rode Trials after that.

                                • 13. Re: Related Data Not Showing
                                  fmpdude

                                  I'm sure you probably know what I'm posting below, so please excuse the simplistic example.

                                   

                                  When I have problems like the ones you're having, I always go and create a simple working example -- the simplest thing I can create to make sure I have the basic idea working correctly. If that works, I add on until I find out where I went wrong.

                                   

                                  So, with that caveat....

                                   

                                  If you create a table like this:

                                   

                                  CUSTOMER

                                  CUS_PK

                                  CUS_NAME

                                   

                                  and an orders table like this:

                                   

                                  ORDERS

                                  ORD_PK

                                  ORD_NUMBER

                                  ORD_CUSFK

                                  ORD_NAME  (Normally, we would have an ORDER_ITEMS table here, but for this example, omitted it)

                                   

                                  With data like this:

                                   

                                  CUSTOMER

                                  1    FRED

                                  2      Bill

                                   

                                  ORDERS

                                  1    1    1  "ORDER 1 CUSTOMER 1"

                                  2    2    1  "ORDER 2 CUSTOMER 1"

                                  3    2    1  "ORDER 1 CUSTOMER 2"

                                   

                                  Then, in the Relationship graph, link the CUS_PK to the ORD_CUSFK, like this:

                                   

                                  Create join settings like this:

                                   

                                   

                                  Now, create a Portal on the main layout and add fields from the related table (ORDERS), like this:

                                   

                                   

                                  After adding a few records, I see this:

                                   

                                   

                                  Scrolling back to the first record, you now only see those related records from ORDERS.

                                   

                                   

                                  Of course, in a real application, you probably would never display the CUS_PK in the related table (ORD_CUSFK as the portal field), but I left it here for an example.

                                   

                                  Hope this additional example is helpful in some way.

                                  1 of 1 people found this helpful
                                  • 14. Re: Related Data Not Showing
                                    fmpdude

                                    Wow, take me back to my own road racing days...

                                    1 2 Previous Next