Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Calculation based on lookups


Recommended Posts

Hello, I'm hoping someone can help me. I was trying to develop a solution for work and I've been pulling my hair out for nearly a month now.


We need a task management / time sheet solution. We need to track what tasks are performed on which days, who performed them, the number of hours for that day, and the type of work done (could be regular pay rate, detail work, or an on-call stipend). There are only a handful of employees. Each may only work a couple days a week or not at all for weeks at a time. The tasks performed usually fall under the regular pay rate category, but occasionally falls under detail work where every employee earns the same detail rate. The detail rate may change a few times a year and the regular pay rates for the employees changes once or twice each year. On top of this is a weekly stipend that goes mostly to one employee, but sometimes others, and sometimes divided among two or more. Then there's the accounting - the detail rate must be paid from a different account and thus needs to be tracked separately.


I have worked on this for quite some time and have a solution but not without its kinks - and one that I cannot work out. So I'll try to explain what I have so far:


A Tasks table that tracks the date, the payroll end date (based on the date), work description, work type, and right now an account type (based on the work type).

A Personnel table for peoples names (address, etc), employee number, regular pay rate

An Assignments join table tying tasks to personnel, hours worked, detail pay rate (lookup), regular pay rate (lookup), hourly pay rate (auto enter calc), stipend, pay, and a couple summary fields (pay, hours, stipend)

And finally an accounting table with the two records for the two account type info.


My main problem right now (as I see it, maybe someone will tell me to start over!) is the hourly pay rate in the assignments table. I wanted to use a calc and determine which pay rate to use depending on the work type (from the task record) - it doesn't work. I've tried both an If and Case statement { If Tasks::Work Category = "Detail" ; Detail Pay Rate ; Regular Pay Rate }. Using this the calc always defaults to the Regular Pay Rate. Is it because both of the results are based on lookups? If I switch the results to random numbers the calc seems to work...


Please let me know if I need to give more information. I'd appreciate any and all help on this, thank you!

Link to comment
Share on other sites

Hi Techphan,


The Tasks and Assignments tables are related by Task ID in both tables. I had thought about using an account type key as well, but for now its a simple relationship.

The hourly rate calc is in the Assignments table and uses results based on lookups that are also in the Assignments table. The calc does look to the Tasks table to see what type of work is being done.


However, I'm trying to get this to work on a Tasks layout in an Assignments portal. Is that the problem?


Edit// I believe I have it working now. I made two changes: the first was to test on the category of work in the Tasks table (as stated above, but had been on account type in Assignments prior), and the second was to uncheck the 'Do not replace existing value of field' for the Hourly Rate calc.


Edit2// Not working :(

Link to comment
Share on other sites

Update: Thanks Techphan and anyone else looking at this, but I believe I have it working now. I stripped out all unnecessary fields and redid the relationships (basically started over) and all is working now. I believe the problem was in the way I was creating records in the portal - I've eliminated that.

Thanks again,


Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

Terms of Use