Subtract 2 feilds on layout and display

i need to subtract 1 field from another field in2 seperate tables and display it on a layout.
techpr0Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jvaldesCommented:
Make sure you create a relationship between the tables that would define how the values in one table are related to the values on the other table first. Then create a new field on the original table, set it to calculation field and in the calculation subtract the values. Make sure you provide the right data type ie, text, number etc.

The new field should have that difference.
0
techpr0Author Commented:
i was able to get it to work kinda but not totally

This is sample data
Table 1
Name       Date         Hours Worked    
J. Doe1     4/20/10          9

Table 2
Name       Date         Hours Worked    
J. Doe1     4/20/10          3.5
J. Doe1     4/20/10             4

It should give me 1.5

These are the 2 ways that i am trying to
This is the calculation-------Time Clock::Regular Hours-Sum Daily Reg. Hours
The result is a regular hours - sum of all hours in the layout  giving me 344

This is the calculation-------Time Clock::Regular Hours-Daily Reg. Hours
The result is It only takes the 4 instead of adding and getting 7.5
0
jvaldesCommented:
If there is more than one entry in the table it will only use the first entry. This is normal. To get what you want add a summary field to Table 2 and tell it to show the sum of Hours worked. Then when you subtract subtract the summary instead of the hours Worked
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

techpr0Author Commented:
I tried that previously below and got the result that follows.

This is the calculation-------Time Clock::Regular Hours-Sum Daily Reg. Hours
The result is a regular hours - sum of all hours in the layout  giving me 344


Sum Daily Reg. Hours is the summary field
0
jvaldesCommented:
Sorry I missed explained. The summary field would be in the first table and would sum up the related values from the related table.

My error
0
techpr0Author Commented:
ok. i think i get where you are going with this. Good solution. but 1 problem

table 1 and table 2 are linked by employee no.

I pick a particular date out of another program and import it into table 1, but the import doesn't have a date field. (can't add date field)
table 2 has employee timeclock information over time since the company was created so it has a date field.

So without the date field table 1 how will it know which record to compare it to. Wouldn't it sum up all hours ever worked for that particular employee
0
techpr0Author Commented:
Also, i'm still trying to test it to see what happens, but how do i make a summary field that is link to a table in another database? summary is only letting me pick fields within that table.
0
jvaldesCommented:
If you provide the database, I can help you solve the problem.
0
techpr0Author Commented:
i begin preparing the database for you and realized that it isn't possible. It is linke to 15 other databases, password protected, and i would need to empty to much information. would take days and I need a solution to this by tomorrow
0
jvaldesCommented:
Prepare a mock up with the three tables and what you are trying to do, it shoukld take a shorter time to do that.
0
techpr0Author Commented:
here is the mockup. If you look in test 2 you will see the "Sum of Daily Hours", Sum of Overtime", and "Sum of Daily Total Hours" is in correct. For Instance.

Employee No. 2 (Tim Bass) for 4/20/2010 should be the following because there is 2 records for that day.
Sum of Daily Hours - 8
Sum of Overtime -  2
Sum of Daily Total Hours - 10

Please let me know if you need a better explanation and Thanks for the help so far
Test1.fp7
Test2.fp7
0
jvaldesCommented:
I think I understood the data backwards but if you look at Test 2 and modify the gDate it will modify the summary of the hours in Test 1


Test2.fp7
Test1.fp7
0
techpr0Author Commented:
I just reviewed the the files and i'm more confused than ever.Test 2 can have many records per employee. Test 1 only has 1 employee. The totals that i would expect in your example is still off.

Also, how did you get the date field in test 1 was this a calculation off of test 2 or did you just put this in based on the info. the i gave you for test 2's info.
0
jvaldesCommented:
I put in some dates to make the model work, I could not figure out which was your employee database. To make this work you need each time record to have a date,employee number and the hours and the summary fields, total hours etc.

On the employee database you need the employee number and a global field with the date that you want to change to adjust the sums.

Look at the relationship I made between the global date that sets the filter and the date n the transaction which is the field that is different by hour record.

If you want to summarize data it is important to put the summarys in the table that is being filtered not the employee table. Then place the related field in the employee layout to see the filtered summary
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
techpr0Author Commented:
I think I have to many scenarios to get around. Is it possible for you to get the 2 databases working correctly that i sent earlier this way i could use this to figure things out.


Let me try and sum up the whole project to see if it clears up my predicament
For the most part, I am tasked with comparing filemakers employee time clock information(test 2) with a new timeclock system recently purchased.  I exported the new timeclock system to a text file. The fields that are exported is employee no, name, regular hours, and overtime hours for the given date i choose to export, but it doesn't export out a date. This is when i created test 1 to store the text file data in filemaker.

The script steps are
1. delete All   <--from test 1
2. import text file<--to test 1
3. prompts them to enter date in filemaker <--used to filter test 2 data
4. show the layout with the difference between both timeclock hours

0
webwyzsystemsCommented:
I would add in a date column after importing. Since you are prompting anyways - when the user imports, just get the date of records from the user. Then use REPLACE after the import is done on a date field in Table1. This basically will add a column for you, populated with the date of all the records you import.
If you do this, then your data model will work properly. You can add in the ability to do a date range or a single date, you can also keep the original "real" records on file, and live in the system.

I don't see a reason to split this raw data up from the main file. Just add a "raw data" table to Table2. Then, import into that.
It seems like you are trying to engineer a system around an incomplete data set. This means you are programming in work-arounds. Look at the complexity introduced, just because you lack a date field!

It's better to get the data set "correct" and then use a common design pattern. (what you are trying to do is "almost" a very common design pattern used in payroll systems around the world.)

This is what jvaldes was getting at in his solution. He implemented the common pattern around a good data set...and it works!
0
techpr0Author Commented:
new to filemaker. so everything that is told to me I have to play with to get it to work.

How do i get the date value entered by the user in the layout to table1's date field?
0
techpr0Author Commented:
I was finally able to get the guy to import the date.

So now i have a relationship on the date fields and the employee number, but i am still getting the wrong number when calculating the difference between the regular hours in both tables.

I am doing the following calculation
Regular Hours - Time Tracking System::Sum Daily Reg. Hours
which would be
10-8=2
but instead I get
10-8= 10

so it must not see the other table field or something i am not thinking of.... Please help
0
techpr0Author Commented:
Here is the record
employee no.:    1
Name:               John Doe
Regular Hours: 10
Filemaker Regular:
Regular Variance : 10
Date: 4/20/2010

If you will notice the filemaker regular is empty. but it should have pulled the related field from the other table.
0
techpr0Author Commented:
Nevermind. i think i may have it so. I will issue out the points tomorrow just to be on the safe side. Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.