12 Replies Latest reply on Mar 22, 2011 12:23 PM by EmilyShep

    Automatically Enter Value based  on Contents in another Field

    EmilyShep

      Title

      Automatically Enter Value based  on Contents in another Field

      Post

      I'm not sure if its just because its Monday but I am having a hard time getting this to do what I want it to.

      Basically I have a program that allows you to enter time sheets for multpile people, right now I have a value list (from a field) that allows the user to pick a cost code from a drop d own menu. What I want to happen now is that once that code is selected it will automatically populate which job number that code relates to. I have used repeating fields for the codes & hours (each employee can have up to 20 codes/week, couldn't think of another way to get that to work) so I want another repeating field that will populate the job number based on the each code that is entered.

      Any help would be greatly appreciated!

        • 1. Re: Automatically Enter Value based  on Contents in another Field
          philmodjunk

          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.

          • 2. Re: Automatically Enter Value based  on Contents in another Field
            EmilyShep

            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...

            • 3. Re: Automatically Enter Value based  on Contents in another Field
              philmodjunk

              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.

              • 4. Re: Automatically Enter Value based  on Contents in another Field
                EmilyShep

                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...     

                • 5. Re: Automatically Enter Value based  on Contents in another Field
                  philmodjunk

                  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.

                  • 6. Re: Automatically Enter Value based  on Contents in another Field
                    EmilyShep

                    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:

                    WorkDays::WorkDate

                    Project No

                    Cost Code

                    Hours....etc

                    I see how I can make 1 entry... how would I make multiple?

                    • 7. Re: Automatically Enter Value based  on Contents in another Field
                      philmodjunk

                      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.

                      • 8. Re: Automatically Enter Value based  on Contents in another Field
                        EmilyShep

                        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

                        • 9. Re: Automatically Enter Value based  on Contents in another Field
                          philmodjunk

                          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.

                          • 10. Re: Automatically Enter Value based  on Contents in another Field
                            EmilyShep

                            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!

                            • 11. Re: Automatically Enter Value based  on Contents in another Field
                              philmodjunk

                              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)

                              • 12. Re: Automatically Enter Value based  on Contents in another Field
                                EmilyShep

                                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:)