1 Reply Latest reply on May 20, 2013 7:20 AM by philmodjunk

    Help regarding relationships

    NathanVeitch

      Title

      Help regarding relationships

      Post

            

           Hi There,

            

           I need to find out some information regading a progam I am developing. It has to do with timesheets, taxes and logging hours. I have created an employees, staff register and tax table. I have been able to get it working where I can log the start and end times for the employees in the staff register table and that displays nicely on the portal. The tax table I did separate as I am more than likely going to build accounting into the system at a later date, so that table holds all the fields with the various tax fields needed. I have linked the tables Employee::pk_EmpID--<StaffReg::fkEmpID and Tax::fkReg>--StaffReg::pk_RegID. 

            

           On a layout based on Employee I have placed a portal from Staff register to display the hours being logged in and out per day. This is working nicely to view the lines per day, I need to now make a new layout where I can view per day, the total spent on wages. I tried creating a layout based on staff register with a staff reg portal from a new Staff reg TO and I linked it StaffReg::RegID--<StaffRegDay::Date and then the other way around, but I get no joy in the portal. The information is there in the staff reg table, what day the employee was clocked in and out and what the pay for that day is, I am just battling to get a portal to display per day the totals for all the employees for that day. Once I get that working then I can easliy do a sum total for the month to date out side the portal at the bottom. 

            

           That is the first problem I am having, the for the second part of my program, the taxes, I'm sure it is going to be an easy thing, which is why I am just looking for info on how I should go about setting it up. I have created a separate tax table which will mainly be used to hold the tax calculation fields that are needed to work out the various taxes paid by either the employee or the company, for the month. To me this seems the best option, cause then I would link the tax table to the employee and staff reg. Then i can maybe have a button to populate the tax table at the end of the month and I can then save a record per employee for their tax for the month. I'm sure this may seem simply, but it is the first time I am doing this, and seeing as I might be building the companies accounting system in a later phase, I want to make sure that I am not going to run into problems down the road. Well I want to try and aviod as many problems by building it proprely from the start. 

            

           Any advice would be greatly appreciated.

        • 1. Re: Help regarding relationships
          philmodjunk

               For your total wages cost, design a list view type summary table based on Staff Register.

               Use summary fields to compute any daily totals such as total cost, total hours, etc.

               Remove the body from this layout.

               Replace it with a sub summary field "when sorted by Date" (you need a date field with the day's date for this.)

               Place the date field and your summary fields in this sub summary part.

               Perform a find to find the records you want (maybe all the records for a month, quarter, year...

               and then sort them by this date field.

               This will produce a report with one row for each day with daily totals shown in the row.