4 Replies Latest reply on Jun 26, 2010 7:22 AM by InvectusIlumni

    Database relationship graph help

    InvectusIlumni

      Title

      Database relationship graph help

      Post

      Hey guys,

       

      I have a very simple solution made of 3 main tables: EMPLOYEES, PUNCHCARD and PAYMENTS.

       

      The PUCHCARD table keeps track of employees worked hours and the PAYMENTS one is responsible for recording payments owed or made to a given an employee based on worked hours.

       

      I have a one->many relationship between the EMPLOYEES table and the PUNCHCARD table

      and a one->many relation between the PUNCHCARD table and the PAYMENTS table.

       

      When I assign a punchcard record to an employee ID, I wanna be able to retrieve some of that employee's related data (name, phone, ...) from the EMPLOYEES table and display it in a report sheet that reads from the PUNCHCARD table.

       

      As it stands currently, those related fields do not display anything when the call is made.

       

      I hope I'm making some sense

       

      Thanks a lot

        • 1. Re: Database relationship graph help
          LaRetta_1

          "As it stands currently, those related fields do not display anything when the call is made."

           

          I'm unsure what you mean by 'when a call is made' but you won't need the employee's phone field in Punchcard; you can cross-place the fields instead.

           

          Your employees is the 'one' side to punchcard's 'many'.  So, while on punchcard layout while in layout mode, select Insert > Field and at top, select Employees from drop-down and select phone field.  If you do not want employees to change the phone field, you can use merge field or set the field to not allow entry in browse mode.

           

          Cross-placing related fields always works.  If you are standing in the 'many' side, grabbing the 'one' side you will get exactly what you want.  If you are standing in the 'one' side, grabbing the 'many' side will always give you the first related 'many' record (depending upon the relationship sort or by natural sort order of creation date if relationship isn't sorted).

           

          One caution about cross-placing .... there is a potential gotcha with what I call the grandchild issue because of perspective but I won't go into that here.  But with child/parent relationships, you can always trust cross-placing to follow these rules.  The only time you might need to add same fields into a related table is if you need to plant the value for historical purpose, ie, customer address for shipment of products in case the customer changes later.  These are business rules which have nothing to do with design but you need to know the answer before you decide whether to cross-place or duplicate data.  In most instances, cross-placing is the way to go.

          • 2. Re: Database relationship graph help
            InvectusIlumni

            Hey 

             

            I now only have 2 tables : EMPLOYEES and TRANSACTIONS with a 1-MANY relationship.

            The EMPLOYEES table being the one and the transctons table, the many.
            When I'm using merge fields on the TRANSACTIONS table to retrieve related data from the EMPLOYEES, , the data is not displayed.
            What Am I doing wrong? 

            The EMPLOYEES table being the one and the transctons table, the many.
            When I'm using merge fields on the TRANSACTIONS table to retrieve related data from the EMPLOYEES, , the data is not displayed.
            What Am I doing wrong? 

             

            • 3. Re: Database relationship graph help
              LaRetta_1

              That should work fine.  Does the relationship work normally, ie, if you place a field on your Punchcard layout, does it show the name?

               

              Also, the report - does it have leading parts?  If so, nothing will show until it is sorted by the break field (the field specified in the leading part).  If you have an employee assigned to this punchcard, try creating a form layout of punchcard and place the employee fields on it.  Do they show?

               

              If the answers are no then verify your relationship and that the field types of the IDs are the same and manually click into the Employee ID field on each side and make sure there are no spaces, carriage returns etc and that they match exactly.

              • 4. Re: Database relationship graph help
                InvectusIlumni

                Thanks a lot.

                 

                I should point out that the report layout is in fact a LIST layout with a header, body, trailing grand summary and footer parts. I'm trying to specify a sorting filed clockin day) and I'll see what it does