|
|
Joined: Sep 2006
Posts: 1,711
Dawg Talker
|
OP
Dawg Talker
Joined: Sep 2006
Posts: 1,711 |
i'm working on a new database for my work....and while i'm pretty good at basic stuff, i'm kinda at a loss with a new database i'm trying to create...
i'm trying to design a database that will track overtime for our department
the member will fill out a form with the amount of time worked, and then from that i need to be able to generate a list that shows each member of the department sorted by "hours worked" and then "senority"
basically at the first of the year the list goes by senority, then from there, hours worked....
the problem i'm having is figuring out how to get just the summary calculations into a list that shows the workers with just there total hours...
i know i can easily sort by name and get a list of all the different ot occurances, and from there i can total each member, but i'm at a loss as to generate a list of the totals without all the individual occurances...
i originally made a record for each individual member, and was trying to get the summary totals into those, but when i try to separate the records to generate the list it doesn't look at the omitted records...
i'm using an older version of filemaker 4.0
any ideas would be great, cause i'm missing something....
Attitude is everything....FEAR THE ELF!!!
|
|
|
|
|
Joined: Sep 2006
Posts: 28,157 Likes: 838
Legend
|
|
Legend
Joined: Sep 2006
Posts: 28,157 Likes: 838 |
well, I'm not entirely certain I followed what it is you are trying to do, but if it was SQL I could help... I'm of no use with Filemaker, though. I would think that you would want at least one table kinda like this: Code:
id | employee_name | date_added | hours | employment_start_date
Then you would have a separate record for each bunch of hours recorded. The id field would be your unique index to prevent dupe records. This layout would also lend itself to other reports like total hours per employee or all hours for a specific date or a date range. Then to get the data, you would do a query using Group By such as this: Code:
SELECT employee_name, sum(hours) as TotalHours
FROM table_name
GROUP BY employee_name
ORDER BY sum(hours) DESC, DateDiff(dd,employment_start_date,getdate()) DESC
Browns is the Browns
... there goes Joe Thomas, the best there ever was in this game.
|
|
|
|
|
Joined: Sep 2006
Posts: 1,711
Dawg Talker
|
|
OP
Dawg Talker
Joined: Sep 2006
Posts: 1,711 |
what type of dbase is that? access?
i've never messed with sql...
we do have a program called firehouse that is based off foxpro...and i beliee it uses sql...or a variation of it...
i've got the inital table done....it has all the info i need...they are called layouts in filemaker, but its the same thing imo....
the problem i have is i can't pull the data out like i want...
for example if i create a field that totals the 'time worked' field then it does it for all employee's not individual...
if i put that same field into the sub-summary(when sorted by 'name' part of the layout, and then i sort by 'name' it will give the total hours for each employee, but it will also list each record for that employee...
employee a 5 hours
employee a 2 hours
total 7 hours
employee b 2 hours
employee b 3 hours
total 5 hours
what i need is a form that would only show
employee b 5 hours
employee a 7 hours
maybe its just time for me to move to a more powerful dbase, but sometimes getting that stuff bought is a hassle...
Attitude is everything....FEAR THE ELF!!!
|
|
|
|
|
Joined: Sep 2006
Posts: 28,157 Likes: 838
Legend
|
|
Legend
Joined: Sep 2006
Posts: 28,157 Likes: 838 |
that is Microsoft SQL Server...well, to be specific, it is the Transact-SQL language written to be run against a Microsoft SQL Server. I can't help much with Filemaker, but perhaps their Knowledgebase would be the best place to start? http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/enduser/std_alp.php
Browns is the Browns
... there goes Joe Thomas, the best there ever was in this game.
|
|
|
|
|
Joined: Sep 2006
Posts: 28,157 Likes: 838
Legend
|
|
Legend
Joined: Sep 2006
Posts: 28,157 Likes: 838 |
ok, just read your post again.... the problem where it is not totalling by employee, but totalling all is why my query uses the GROUP BY line... it groups the results by employee name, then it sums the hours for each group..... you should see if you can find any examples of similar syntax.
Browns is the Browns
... there goes Joe Thomas, the best there ever was in this game.
|
|
|
|
|
Joined: Jan 2007
Posts: 832
All Pro
|
|
All Pro
Joined: Jan 2007
Posts: 832 |
I work with FileMaker Pro everyday. What version are you using?
|
|
|
|
|
Joined: Sep 2006
Posts: 28,157 Likes: 838
Legend
|
|
Legend
Joined: Sep 2006
Posts: 28,157 Likes: 838 |
Quote:
maybe its just time for me to move to a more powerful dbase, but sometimes getting that stuff bought is a hassle...
Look into MySQL... it is a very fast, powerful and most importantly FREE SQL Server. ... though the licensing may have changed a bit if done for commercial use, but it is a free download ( http://www.mysql.com ).
Then there is PostgreSQL.... also free, and open source ( http://www.postgresql.org/ ) .. I've never used PostgreSQL, but it has a decent reputation as best I can tell (though it seems MySQL is more popular).
Either way, both should be more than powerfule enough for what you are doing and they are FAST with low overhead.
Browns is the Browns
... there goes Joe Thomas, the best there ever was in this game.
|
|
|
|
|
Joined: Sep 2006
Posts: 1,711
Dawg Talker
|
|
OP
Dawg Talker
Joined: Sep 2006
Posts: 1,711 |
4.0
i want the chief to upgrade, but he won't....
i created alot of databases, but it was about 8 years ago....lately i've been just keeping them going....
i'm thinking that i may need to have two databases one with just 24 records (24 firefighters)
and another with the data entry....
date
name
start time
end time
total hours worked
then i just have to find a way to pull total hours worked by employee to generate a list from least to most hours
the list also has to take into account senority
in other words when we all have zero hours, the senior guy gets called first....then moves to the bottom, as he has some hours....
Attitude is everything....FEAR THE ELF!!!
|
|
|
|
|
Joined: Sep 2006
Posts: 1,711
Dawg Talker
|
|
OP
Dawg Talker
Joined: Sep 2006
Posts: 1,711 |
thanks purp...i'll have to look into that...i also wanna see what that firehouse program runs on....
i thought i read foxpro....it has a way to generate reports that has syntax very similar to the way you posted...it might be possible to use that....
the problem is we have had 2 dbases around here...first filemaker....
then this firehouse program....
we also have some spreadsheets that do various things....
the spreadsheet is how we make our recal list now....i'm trying to use filemaker to make it better...
it would be nice to be able to generate lists any way that we want, and automatically print out various lists....
with the spreadsheet we have to cut and past data, and sort it...its not a terrible pain, but there has to be a better way....
Attitude is everything....FEAR THE ELF!!!
|
|
|
|
|
Joined: Sep 2006
Posts: 4,480 Likes: 26
Hall of Famer
|
|
Hall of Famer
Joined: Sep 2006
Posts: 4,480 Likes: 26 |
Since MySQL can do stored procedures now (didn't used to until a the most recent major release a year or so ago) I'd go with it over Postgres. I LOVE Postgres, and have used it for years, but it is being beat out by MySQL as far as the corporate world goes. They are both good products, and both free, as long as you adhere to the gpl licensing agreement - http://gplv3.fsf.org/
#gmstrong
|
|
|
|
|
Joined: Sep 2006
Posts: 2,667 Likes: 53
Dawg Talker
|
|
Dawg Talker
Joined: Sep 2006
Posts: 2,667 Likes: 53 |
Microsofts SQL Server MSDE or MS SQL Server Express(newer version) is also free. And it adheres to about the same restrictions as MySQL. Now I am not sure about the newest version(Express) but I do know that the performance for the MSDE starts to be limited after a certain amount of users logged in. But for small applications it can be a VERY powerful tool. Much will depend on what you are comfortable using....MySQL is a very nice tool....but I did like the visual interfaces that Microsoft had for SQL Server. They do make GUI apps for MYSQL. But for me coming from an Access SQL Server background...They a did not seem as intuitive...but then nothing will once you have grown with a certain app....LOL.... MySQL is a GREAT DB....but if you are new to T-SQL language, it may not be the best to start with if you don't have a GUI app for it. You also want to becareful to not use the DB in a way that violates the agreement that allows it to be free. I lean toward SQL Server as that is what I grew as a developer using...but I can't say I have anything really bad to say about MySQL.... check this link for MS SQL Server Express... http://www.microsoft.com/sql/editions/express/default.mspx
I thought I was wrong once....but I was mistaken...
What's the use of wearing your lucky rocketship underpants if nobody wants to see them????
|
|
|
|
|
Joined: Sep 2006
Posts: 2,667 Likes: 53
Dawg Talker
|
|
Dawg Talker
Joined: Sep 2006
Posts: 2,667 Likes: 53 |
Quote:
Since MySQL can do stored procedures now (didn't used to until a the most recent major release a year or so ago)
Great point...I was lucky in a way and did not have to experience MySQL without that ability...I would never had even considered it had I known you couldn't create a stored procedure on the backend with the ease of SQL Server.....and if I had done that I would have missed out on a good product.
I thought I was wrong once....but I was mistaken...
What's the use of wearing your lucky rocketship underpants if nobody wants to see them????
|
|
|
|
|
Joined: Sep 2006
Posts: 2,667 Likes: 53
Dawg Talker
|
|
Dawg Talker
Joined: Sep 2006
Posts: 2,667 Likes: 53 |
Quote:
in other words when we all have zero hours, the senior guy gets called first....then moves to the bottom, as he has some hours....
This definitely sounds like a stored procedure/trigger type of solution.....If I am getting the gist of your dilema you are trying to find out who to call(or at least the order of the calls). And since you have multiple factors in that equation, a stored procedure is the best way to program that logic.....Unless there is a field/column in your table design that designates each persons seniority(that is the seniority is based upon a value entered into the table and not calculated....), then I think a stored proc is the way to go....Because you really have to watch how data is entered into your table if the seniority is entered ito the table and not "calculated"
I thought I was wrong once....but I was mistaken...
What's the use of wearing your lucky rocketship underpants if nobody wants to see them????
|
|
|
|
|
Joined: Sep 2006
Posts: 4,480 Likes: 26
Hall of Famer
|
|
Hall of Famer
Joined: Sep 2006
Posts: 4,480 Likes: 26 |
With SQL Express, there is a 4MB limit on database size, so you need to be certain that your DB will never exceed this. There are also some other limitations as far as processor and memory utilization, but don't recall those off the top of my head. All of the Microsoft "express" editions of their .NET tools and SQL are very nice and also free. For small projects, the toolset is excellent. Oh, and the MSDE version is not supported by MS anylonger. That version has no size limitation, but does have some algorithms that slow the processing down as more concurrent connections are created. There are ways around this via some intuitive coding of data access  which is why Microsoft nixed the concurrency limitiation and went to a size limitiation for SQL Express.
Last edited by ColumbusDawg; 08/01/07 06:53 AM.
|
|
|
|
|
Joined: Jan 2007
Posts: 832
All Pro
|
|
All Pro
Joined: Jan 2007
Posts: 832 |
Ok, I haven't used FMP 4 in a few years... but, you could create a calculation field that combines seniority and over time hours.
It would probably be best to create to create a second database that stores the totals/summaries. This database would really only need 1 record and have a field for each of the firefighters. (Define Relationship)
Create a script in DB-1 that gets the summary of FireFighter_1(seniority + overtime) and pastes it into DB-2 ff1 field. Repeat for all firefighters.
Then, with all totals in db-2, it is easy to perform a sort.
|
|
|
|
|
Joined: Sep 2006
Posts: 1,711
Dawg Talker
|
|
OP
Dawg Talker
Joined: Sep 2006
Posts: 1,711 |
Quote:
Ok, I haven't used FMP 4 in a few years... but, you could create a calculation field that combines seniority and over time hours.
It would probably be best to create to create a second database that stores the totals/summaries. This database would really only need 1 record and have a field for each of the firefighters. (Define Relationship)
that was my latest train of thought...i have used other dbases to pull info for a certain entry but never transfered data like you are saying....
what i originally tryed to do was create 24 records (1 for each firefighter) and added a senority field into a layout and enter 1-24 for senority....then i used a find for ">0" in that field to keep those records separate...problem was that the omitted records weren't in any calculations....
i never thought about using a calc to combine the 2 values i guess i could easily pick a value bigger than the biggest overtime amount (usually 8 hours) add it in and then subtract it in the other dbase for the actual hours...
do i have to use a script? wouldn't a 'define relationship work too' and be in real time?
i would certainly use a script to print the various forms out.....
Quote:
Create a script in DB-1 that gets the summary of FireFighter_1(seniority + overtime) and pastes it into DB-2 ff1 field. Repeat for all firefighters.
i think i used a calculation with getsummary (total hours worked,name) where it does the calculation when sorted by name
so now that i think about it a script would work great...( like you said  )
i could make it sort by 'name'...but instead of using the 'total hours worked' field use the calculation that combines 'senority' and 'total hours worked'
get that info into the other dbase (with a relationship) or do i use export data?
that is one thing i never did was export stuff....
maybe export data and 'overwrite found set'?
Attitude is everything....FEAR THE ELF!!!
|
|
|
|
|
Joined: Sep 2006
Posts: 1,711
Dawg Talker
|
|
OP
Dawg Talker
Joined: Sep 2006
Posts: 1,711 |
Quote:
.If I am getting the gist of your dilema you are trying to find out who to call(or at least the order of the calls). And since you have multiple factors in that equation, a stored procedure is the best way to program that logic.
thats exactly what i'm trying to do....i also need to generate two different lists but i got that.....one for emergencys and one for when guys call in sick....
i had a field for 'senority' but i was looking at it the wrong way...i think merth hit the nail on the head.....
Attitude is everything....FEAR THE ELF!!!
|
|
|
|
|
Joined: Sep 2006
Posts: 2,667 Likes: 53
Dawg Talker
|
|
Dawg Talker
Joined: Sep 2006
Posts: 2,667 Likes: 53 |
I believe that is 4 GB not MB....a 4 GB Database is a Biggun and plenty of space for what he is trying to do...
But yep...I know what you are saying...
Yeah I also saw they weren't supporting MSDE as well...and it won't even run on Vista.....
I remember as we got more than 5 or 6 people banging a little on MSDE that performance went to heck and so I upgraded them to full blown SQL Server...and bam.... things went like lightning...still runs solid....Personally I haven't been able to utilize the full power of it especially when you get into the processor and memory utilizations...I will be taking some classes in some of that soon as eventually I want to get my MCSD.....But first I need to pay for my wedding...LOL To this point I am just trying to write good procs, triggers etc....have good solid DB design(IMO the most important thing is your table design and data flow design) etc. and soon I will start concentrating on the Analyzer and the tricks we can play with in there....
I thought I was wrong once....but I was mistaken...
What's the use of wearing your lucky rocketship underpants if nobody wants to see them????
|
|
|
|
|
Joined: Sep 2006
Posts: 1,711
Dawg Talker
|
|
OP
Dawg Talker
Joined: Sep 2006
Posts: 1,711 |
pete, your talking way over my head unfortunately i'm pretty much having to do this on filemaker....my only regret is that i wish i had a newer version....i think it does more than the older one i have does..... my biggest problem is thinking a project thru....i usually just go with an idea and see where it ends up, as apposed to being really trained in doing this, and making say a flow chart....and then creating the dbase.... back a few years ago i created dbases for everything we did at the firestation.... that has since be replaced with a program called 'firehouse' that runs off a foxpro based dbase..... we still use some of my programs for special things.... right now i'm trying to get the chief to upgrade, because we have some data redundancy, that i don't want....basically i'm bored
Attitude is everything....FEAR THE ELF!!!
|
|
|
|
|
Joined: Jan 2007
Posts: 832
All Pro
|
|
All Pro
Joined: Jan 2007
Posts: 832 |
The define relationship would work fine. What I was suggesting was a script in db-1 to COPY field ff1 and then call an external script (db-2) that PASTES the total hours/calculation into db-2 ff1 field.
The export features are very deep. You can export a 'merge' file and then use a batch/applescript to open Microsoft Word - format the document - print the document.
I have some thing similar that logs my total records counts for various databases.
FileMaker 4 can handle what you are attempting. I just got my copy of FMP 9 on Monday. I still have not jumped into it. Most of my files are still in FMP 6.
|
|
|
|
|
Joined: Sep 2006
Posts: 4,480 Likes: 26
Hall of Famer
|
|
Hall of Famer
Joined: Sep 2006
Posts: 4,480 Likes: 26 |
Duh. 4GB - 4MB would be not allow much data in an MS SQL environment!  It basically keeps you from developing applications that should use the full blown SQL Server by utilizing a free tool.
#gmstrong
|
|
|
|
|
Joined: Sep 2006
Posts: 2,667 Likes: 53
Dawg Talker
|
|
Dawg Talker
Joined: Sep 2006
Posts: 2,667 Likes: 53 |
Quote:
Duh. 4GB - 4MB would be not allow much data in an MS SQL environment!
Dang.......remember when 4MB really WAS something....LOL
racer...sorry dude....db stuff...lol
I thought I was wrong once....but I was mistaken...
What's the use of wearing your lucky rocketship underpants if nobody wants to see them????
|
|
|
|
|
Joined: Sep 2006
Posts: 1,711
Dawg Talker
|
|
OP
Dawg Talker
Joined: Sep 2006
Posts: 1,711 |
no problem....i can remember when a 1200 baud modem was the rage.... or for that matter when i was hired at the fire department the database was a book...done on a typewriter... can't wait to get to work tomorrow, and work on this...
Attitude is everything....FEAR THE ELF!!!
|
|
|
|
|
Joined: Jan 2007
Posts: 832
All Pro
|
|
All Pro
Joined: Jan 2007
Posts: 832 |
racer - if you need any specific pieces of code/script or what-not, shoot me an email. I am glad to help in any way possible.
|
|
|
|
|
Joined: Sep 2006
Posts: 1,711
Dawg Talker
|
|
OP
Dawg Talker
Joined: Sep 2006
Posts: 1,711 |
thanks....let me see where i get tomorrow...
Attitude is everything....FEAR THE ELF!!!
|
|
|
DawgTalkers.net
Forums DawgTalk Tailgate Forum any filemaker pro experts out
there?
|
|
