Look Up Values

[TKnTexas]

I want to be able to calculate a payroll check.  I have done the calculation in Excel.  I was able to replicate the calculations in Filemaker, but I am not sure it is the best way.  I used a CASE statement to handle the various level of pay to get a proper withholding rate.  

But each time the tax tables change, the case statements have to be changed.  Is there a better way?  A better way to store the tax information?

[Rick Whitelaw]

Create a table that stores the tax rate. Then Lookup the tax rate from your original table. That way, when the rate changes, you simply change the rate in the rate table. This way you preserve history as each record will look up the tax rate and store it in the current record. Previous records will retain their values.

[TKnTexas]

Thanks.  I will play with this.

[TKnTexas]

 

I have two sets of ranges to put in the table.  The two ranges are Single and Married.  I have set the relationship from Wages in the Calc Table to the TaxTable.  

I get a ZERO for the TaxRate.  So I am not sure what I am doing wrong.  I looked up the Employee PayRate from the Employee Table.  So I thought I understood the concept.  

Right now I only have the Single tax ranges in the table.  I am not sure how to distinguish the look up of wages from single and married?

[LaRetta]

I should add that there are many payroll services and they take the headache of keeping up with tax laws (state and federal), withholding tables, quarterlies payroll taxes, W-2s, tracking requirements for W-4s and they hold the burden of responsibility in case of error.  

 

They are VERY inexpensive compared to the design costs of such a solution in addition to the cost of its administration.  All you really need to track are hours, which you send them - THAT FM can track for you.

 

Even with strong accounting background, I would suggest against rolling your own Payroll because of the complexity and risk.

 

Added - BTW, I've done 5-6 large payroll solutions and I know what is involved and I STILL recommend against rolling your own.

Edited July 12, 2014 by LaRetta

[comment]

I am not sure how to distinguish the look up of wages from single and married?

 

I am not sure what your screen shots show us. It seems to me that the relationship that looks up the tax rate needs to be based on matching both status and amount - and the tax table needs to have a distinct record for each rate.

 

Alternatively, you could use an auto-entered calculation instead of a lookup and pick the appropriate field (single or married) from a common record.

 

 

I should add that ...

 

Something is missing?

[LaRetta]

LOL, I agreed with Rick (by rating UP) but I wanted to add a caution and suggestion not do do payroll at all.  My response DID sound a bit 'out of thin air', I suppose. ;-)

[Rick Whitelaw]

I have to disagree with the idea of avoiding payroll. Payroll is one part of the solution I use to run my business and it's just not that difficult to do properly. If it were difficult I wouldn't be able to do it! Perhaps one difference is that the people I hire are still considered self-employed sub contracted entities and normally do not have taxes deducted at source. However, the devil is always in the exceptions. It's not uncommon for Revenue Canada, a union or the IRS to make a demand for withholding. My software manages to do this as well as write cheques to the appropriate entity as well as print up the tax forms etcetera. While it's certainly important to be aware and up to date with laws and rates it's certainly doable. Last year I used my own software to answer a huge HST audit. The auditor told me it was the easiest audit he had ever done. Even asked for a tour of the solution. To me that's an indication that this can work.

[LaRetta]

Perhaps one difference is that the people I hire are still considered self-employed sub contracted entities and normally do not have taxes deducted at source. However, the devil is always in the exceptions.

 

Self-employed contractors are issued simple 1099s.  It is far from same as employees. Keeping up on the tax rates and rules IS a comprehensive task - just ask any payroll service; just ask any large business who must employee several staff to handle it properly.

 

Do not take my word for it - check out the facts.  It is known true by any accountant. :-)

[Rick Whitelaw]

Agreed on all counts. I know it requires a lot of diligence to run a payroll application. My point is that it's possible to keep up with the rules and implement them in a solution. From a database point of view it's not difficult. Whether a person wants to spend the time staying current or farm the work out is another issue. To have an A to Z solution it's simply necessary to keep up with the current rules. This is much the same as saying that a solution needs to stay current of business practices. They change as do tax rules.

Rick.

[TKnTexas]

For me this is an exercise of learning more about FileMaker Pro than writing a payroll solution.   I have been doing accounting for too many years to not know how complex the tasks are.  There are real world times when a check needs to be cut outside of the payroll process.  This could supplement.  

More importantly the lesson is learning to set up a table of values and being able to look them up.  

[TKnTexas]

My three attachments show the tax table from the IRS.  A screen shot of the relationship screen section, and the last is the table that I have for look up.  

My FileMaker lesson is that using an ID I have always been able to look up fixed data, i.e. employee name, pay rate, addresses, phone numbers.    But for this I need look up the tax rate for $500, which is 15% from the IRS Tax Table. 

Formatting this as a calculation and using a CASE Statement works.  But that is not easily updated.  Hence I want to do it from the table.  And I hopeful I am just overlooking something simple.

I hope the clarifies.  As to the attachments when I click on them they enlarge for viewing.    I do not not what to do differently there.  I appreciate all of the discussion.  I learn more from a real problem than an abstract.

[comment]

Formatting this as a calculation and using a CASE Statement works.

 

That's certainly not what I suggested. Basically, you have two options here: either set up your TaxRates table to look something like this:

 

FromAmount    Status    TaxRate

   0          Single      0%

  43          Single     10%

 218          Single     15%

...

   0          Married     0%

 163          Married    10%

 512          Married    15%

...

 

then look up the appropriate rate using a relationship defined as:

Payroll::EmpStatus = TaxRates::Status
AND
Payroll::GrossAmount ≥ TaxRates::FromAmount

with records from TaxRates sorted by FromAmount, descending.

 

 

Upon closer inspection of the IRS tax table, the second option is not practical because the amount breakpoints are different for the two categories.

[TKnTexas]

Thanks for all of the assistance.  I was not setting the correct options in the LookUp screen.  The relationship was right; the field was right.  But the options to NOT COPY, LOWER or HIGHER were not touched.  Made the difference.  

[comment]

I am not sure we're on the same page here. Having a relationship based on two matchfields, you should NOT use the "copy next lower/higher value" option, because the behavior is unpredictable. Note that "married" is the next lower value relative to "single".

[TKnTexas]

All SINGLE are together and then all MARRIED.  I have the records sorted decreasing order Single and High to Low Amounts followed by Married and High to Low Amounts

 

Does that help the look up?

 

 

Editted. .

[comment]

All SINGLE are together and then all MARRIED.

 

I am not sure what you mean by "together". We are talking about a lookup over a relationship here; the order of the records in the source table (TaxRates) is irrelevant. The important point here is how they are sorted in the relationship's definition. Since the relationship "sees" only records of matching status, the sort order needs to apply only to the FromAmount.

[TKnTexas]

Thank ya.  I am now testing the calculations to verify the correct results are showing.  I appreciate the tips and assistance.