6 Replies Latest reply on Dec 2, 2013 7:52 AM by incybau

    Employees and Bills due relationship

    incybau

      Title

      Employees and Bills due relationship

      Post

      I hope someone can help, I can't seem to figure this one out:

      I have an employee table and some employees are assigned mobile phones with limits, anything over the limit is charged to the employee.  I have another table where all the mobile bills are inputted each month, a record of each bill is made.  

      Now I have no problem getting the history of the bills in a portal in the employee page, but how do create a field that gets the most recent amount due to be charged to the employee based on all the bills table when it comes to payroll day? furthermore, how do you get it to look up the amount every other payday?  Hoping someone can point me in the right direction- thanks!

        • 1. Re: Employees and Bills due relationship
          philmodjunk

               Why "every other payday"?

               Does that mean that your employees are paid Bi-weekly?

               It's possible to set up a relationship to match records by a date, list of dates or a range defined by two dates and then you can get a total cell phone bill total to use to compute the amount the employee is responsible for.

          • 2. Re: Employees and Bills due relationship
            incybau

                 Our Mobile bills are billed monthly but employees choose to have the deductions on the25th of the month (our payroll dates are the 10th and the 25th) will try relationship by date! Thanks!

            • 3. Re: Employees and Bills due relationship
              incybau

                   I hope I can bother you for help again - I'm showing the relationship diagram below.  I had to create a separate table for Mobile phones assigned because at some point, the same number might get transferred to someone else after someone leaves.  So, It works fine, I have all the cell units listed in one table and then from the employee data, I choose the unit assigned to them.  When the bill comes, when the mobile number is chosen all the correct data of the employee it is assigned to comes out.

                   Now, when payroll comes, what I did instead of looking for a date range, I have a field in the Bill area to choose the date when the amount is to be deducted.  Which works fine only to a certain point - through value lists, I am able to choose  which bill record through that date field.  But when I place the field of the actual amount of the bill to be paid from the Bill table, the values don't change depending on the deduction date chosen - could you tell me what I'm connecting wrong?

                   The field on the payroll Data is the CellID_fk with a value list to choose from CellID_pk of the Cell Unit Table and the secondary value from the Bills table to show the date to be paid.

                    

                    

              • 4. Re: Employees and Bills due relationship
                philmodjunk

                     There is nothing in your relationships that matches a record in Bills to any other records by date or date range, only by CellID. You'll need more match fields to match by date or date range.

                • 5. Re: Employees and Bills due relationship
                  incybau

                       Okay, Sorry, I hope you can bear with me as I am more confused now, I'm technically not matching the dates, I'm actually choosing the Bill ID from the Payroll data portion and using the due date field as a guide to know that is the correct record.  I was able to do it in a previous table where in the PO # ID is chosen and then the fields from that record are automatically populated based on the ID, i was attempting to do the same with this, but this time the fields don't get populated when I choose the Bill ID.

                       I tried making another TO occurrence of the Bills ID to relate to the payroll data table but this time no values come out :-(  

                        

                        

                  • 6. Re: Employees and Bills due relationship
                    incybau

                         Thank you so much for your patience - I linked the wrong items - big mistake on my part - the Bill second TO occurrence worked - thanks!