That would be difficult to do.
each employee can have up to 20 codes/week, couldn't think of another way to get that to work
Are you familiar with using portals to related tables? Using a table of related records will make this doable where it will be either difficult or impossible to do with repeating fields.
I couldn't wrap my head around using the portals and related tables for this application... right now I have a layout with all the codes as one field... the hours are input manually each week and in my report the repeating fields are separated and appear on separate lines along with the employee's information on each line...I was just trying to think of another way than having people input the project number manually as well...
Features easy to do with a portal. In a repeating field type approach, you often find they won't work at all, leaving you with the option of not using such a feature, using a set of individual fields, or using the much simpler portal.
I had an invoicing system that used repeating field with 10 repetitions for listing 10 line items in the invoice that I created before I knew better. That setup required 10 different productID fields with 10 separate relationships to the ProductPriceList table and 10 different unit price fields before I was able to get it to work.
When I later changed it into a portal based set up with a related table of line item records, I was able to achieve literally a 10 fold reduction in design complexity for the layout, scripts, reports and calculations.
I guess I just don't really understand how to set up the portal so that it will be helpful (right now my program is pretty complex). The thought of changing how I'm currently doing it makes me excited for it to be more easily programmed but at the same time I can quite figure out where to begin...
the end result needs to produce a list like this:
Project Number Employee ID Work Date Cost Code Hours Position
1000 EMP01 01/03/11 3440 11 1A
1000 EMP01 01/03/11 3650 15 1A
2050 EMP01 01/03/11 1600 5 1A
1000 EMP02 01/03/11 1150 11 1A
1000 EMP02 01/03/11 2620 11 1A .....etc
Do I make one layout with Codes & corresponding Project Numbers... and then make another layout with employee information? My big problem is that one employee can be working on multiple projects in one week...
What's the context for your list of data that we can put in a portal here?
Are these all employee work records for a selected date?
I'll assume that for now as it is consistent with the example data that you posted. If it's not, we can modify accordingly later. Do this in a simple demo file and then you can use what you learn here to modify your working database later once you have this working for you.
You first need the right relationship between tables in order to have a portal that will work for you.
Let's have two tables, WorkDays, EmployeeHours
WorkDays::WorkDate = EmployeeHours::WorkDate
Double click the relationship line and select "Allow creation of records via this relationship" for the EmployeeHours side of the relationship.
Use the portal tool to add a portal to EmployeeHours on a layout based on Workdays.
EmployeeHours should have a field for each column of data shown in your example. Add them all to your portal except WorkDate. You can add WorkDays::WorkDate to your WorkDays layout and this will show the date for all records that you'll see in this portal. You can format the Project Number, Employee ID, position, and cost code fields as drop down lists to speed data entry. (In your full up system, these would be value lists that display data from other tables, but we are keeping this example simple here.)
To log employee hours, you just create a new WorkDays record, enter the desired date (it can be set up to enter today's date automatically), and start entering data in the portal.
You can now define summary fields in EmployeeHours to create summary reports that list all work done by a given employee and/or all work done on a given project or by a specified date or range of dates. If this were working in your current database file, you can use a relationship from an employees table to EmployeeHours to set up a portal that lists only work done by that employee. A in similar fashion you can add a relationship so that a portal on a Projects layout lists work hours specific to that project. All of these are just different ways to access/display the same work hours records from the same EmployeeHours table.
This is just to get you started. Feel free to ask follow up questions as needed.
This is great, thanks for your help, still going to have a few more questions though!
I have all the fields and relationships set up, now this is where I get confused, inputting the hours. If one guy has 5 codes, I want to be able to see all 5 while I'm entering them so that I know what has been entered to date. For instance this is where I would want to have repeating fields haha but I see the benefits in staying away from them but haven't quite figured out how to get around it yet...
Right now, in my EmployeeHours I have:
I see how I can make 1 entry... how would I make multiple?
I'd just have the employee enter the code in 5 rows of the portal, each with the different project and/or cost code.
Summary fields or aggregate functions using Sum() an be used to compute totals and sub totals as needed when you want the total for one employee for the day--to give one example.
Ok maybe I don't understand how a portal works then... I thought the Workdate was the portal? Whats the point of me having a related field with WorkDays::WorkDate & EmployeeHours::WorkDate?
I just tried making a portal for cost code and hours... it won't let me input anything into them
WorkDate is not the portal. It's merely a field used to define the portal's relationship. Portals don't even have names in FileMaker unless you use the Inspector to give them an object name.
WorkDays::WorkDate = EmployeeHours::WorkDate
defines a relationship that links any number of records in EmployeeHours to one record in WorkdDays that has the same date.
If I'm looking at a WorkDays records where WorkDays::WorkDate is 3/31/2011. Then the only records I will see in the portal or EmployeeWorkHours records that are also dated 3/31/2011. This is just one option. I picked it for my example because all your example entries had the same date. You can add as many fields from the EmployeeHours table to the portal as you need. You would not use a portal for just the cost code and hours table so I'm not sure exactly what you tried to do there.
Here's a different way to set up portal to EmployeeHours:
Define an Employees table and set up this relationship in Manage | Database | Relationships:
Employees::EmployeeID = EmployeeHours::EmployeeID (enable allow creation of records for employeeHours in this relationship.)
If you put a portal to EmployeeHours on the Employees layout, you'll be able to enter and see all work records for just that employee, which is why we would use a relationship that matches the records by EmployeeID instead of by WorkDate.
Ahhhaa!! I think I got it!
So now I'm back at my original question... right now I have 3 fields in my portal (project #, cost code, hours) is there a way for me to pick a project number, have the related cost codes show up in the drop down menu? And also in working the other direction, if a guy is only working on one project, can they pick the code and have the project number automatically populate?
Sorry it took me a while, I didn't think about what the portal was actually doing, now I know!
OK, other help could have just complicated the process until we had a good foundation on which to build.
What you are describing is called a conditional value list. If you have the right tables set up, you can indeed select a Project number and then the cost code field will only list cost codes appropriate to that project.
Custom Value List? (forum tutorial)
http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list (knowledge base article)
Thank you sooo much!! This has already cut down the programming involved in half!! So much better than repeating fields! Also thanks for the conditional formatting link, I read through quickly, makes perfect sense! Thanks again:)