1 2 Previous Next 18 Replies Latest reply on May 24, 2010 11:43 AM by philmodjunk

    Repeating Fields as Data Array?



      Repeating Fields as Data Array?


      Looking forward in seeing what kind of response this will generate...


      Trying to work through this design, and will illustrate the scenario:


      Sales company with a dozen employees that distribute up to 4-5 different products.

      Total number of each product distributed is recorded each day.

      The first pay period equals to total of each product (recorded daily) from the 1st of the month to the 15th.

      The second pay period is the same for day 16 - 30 or 31st.


      Does that make sense? :-)


      Currently, all this info is being recorded on a spreadsheet, but this has resulted in tons of Excel worksheets/workbooks, etc.


      In my design, I considered 2 options, one using 2 Repeating Number Fields to record items distributed in each pay period. Very simple to type in a number each day, and it totals up with a summary field. The problem with this is the summary field remains the same for each employee, so obviously I'm clueless somewhere.


      The other option was using 31 individual fields (per each product for each day) and 2 calc fields to total each period. If an employee distributed 5 different products each day, it would take 155 fields and that doesn't seem right either.


      Looking for a better idea from any of the guru's out there.


      Thanks in advance.




        • 1. Re: Repeating Fields as Data Array?

          Have you considered a related table of records for this? That gives you quite a bit more reporting flexibility. There's a horizontal portal trick that can list portal contents in a row--so that's not a problem here. To get a horizontal portal, you place a series of one row portals to the same table occurrence on your layout and specify a different portal row in each. With filemaker 11, you even have the option of simply specifying a different filter expression for each and that may simplify data-entry in some cases.


          I wouldn't ever use individual fields, that's a nightmare to design and maintain.


          If you insist on repeating fields, Sum (repeating field) will compute the total of all the repetitions in the field.

          • 2. Re: Repeating Fields as Data Array?

            I agree about the individual fields, and I'm not too sure about the repeating fields.

            I do know the spreadsheet method is too time consuming, as each month it has to be recreated, and the first day of the month varies, so the formulas get modified, and the person assigned to enter these numbers frequently makes mistakes and screws up data.


            I have a screen shot of the aforementioned spreadsheet, but can't seem to paste it here.


            Question Phil... If an employee delivers, for example, 25-35 each of 3 different products each day during the month, are we talking about 3 separate repeating fields (defined 1-15 & 16-31) to span the entire month?

            • 3. Re: Repeating Fields as Data Array?

              As I said before, I wouldn't do this with repeating fields. I'd set up a portal to a table of related records. It's many times more flexible than repeating fields.

              • 4. Re: Repeating Fields as Data Array?

                Hey Phil,


                Can you elaborate on your suggestion please?


                I always approach a design on paper first, and in FM second. I could not come up with anything that made sense. How can I get a screen shot or an example file posted so I can better illustrate what must be reproduced. At least that way if someone has a better way all together in doing this, that would be great too! lol



                • 5. Re: Repeating Fields as Data Array?

                  How familiar are you with portals?


                  There are many examples of portals out there and some come as templates that are part of your filemaker install.


                  Here's one sample: http://www.4shared.com/file/ecruPBO2/FilteredPortal.html


                  Here's a link describing step by step how to post a picture to your forum messages:

                  Tutorial-How to post a picture so that it shows up in your message

                  • 6. Re: Repeating Fields as Data Array?



                    Thanks for the tip regarding the file share site, pretty nifty!

                    Here is a screenshot of the Excel worksheet I want to convert into an FM solution:






                    • 7. Re: Repeating Fields as Data Array?

                      I agree with Phil. Use portals rather than repeating fields. I DO use repeating fields, but generally only for "display only" situations. As soon as you begin to use repeating fields in situations where calculations on them become important you expose yourself to writing a pile more code than you would using related fields. Repeating fields seem attractive initially but lose their glow quite quickly. This is not to say they're useless . . .



                      • 8. Re: Repeating Fields as Data Array?

                        I tend to agree, but there are some question as to what would be better in this situation...


                        Repeating Fields would be simple for this reason: In my initial design, i defined 2 fields -  

                        PayPeriod1 (n) 15 reps for the first 15 days of the month | and | PayPeriod2 (n) 16 reps for the 16th thru 30th or 31st of the month. Each cell is a separate day and the number of items delivered that day is entered.

                        3 addition fields, two of them sum up the two pay period fields and the last field makes a grand total.


                        That works fine for one distribution employee who delivers only one product... just type in the quantity each day.

                        However, most employees distribute up to four or five different products, each of which must be tallied up separately from the other, as they all have a different pay rate. I can see a layout with up to 10 repeating fields (ex: for an emp that had five products), but that would probably look pretty loud. But it could work, maybe.


                        On the other hand ---


                        Portals - I've struggled with this issue for several hours, and when I envision the design on paper, I draw a blank.

                        What kind of tables would need to be created, how would they relate, and how would they be used? Maybe an EmpTable, ProductTable and a DailyCountTable (for example),  related by???


                        lol, this should be challenging to many, I'm sure...

                        • 9. Re: Repeating Fields as Data Array?

                          Let's take a  simple approach to get things started...


                          I believe you'll need at least 3 tables, likely more.


                          Employees, one record for employee, use a serial number field such as EmployeeID as primary key.

                          Other fields in this table include name, and other contact type info


                          Sales, one record for one type of item sold on a given date

                          Fields: DateSold, ItemID, Qty, EmployeeID

                          Add other fields here as needed to document your sales info.


                          Products, one record per product that you sell

                          ItemID, ItemDescription

                          Add other fields for pricing etc. as needed



                          Employees::EmployeeID = Sales::EmployeeID (Enable allow creation... on Sales table)

                          Sales::ItemID = Products::ItemID


                          Now set up a portal to Sales on a layout based on Employees you can specify that the portal sort entries by DateSold if you prefer.

                          Log sales by entering the DateSold, ItemID and Qty. You can add the Description field from Products to this layout to help see what item goes with a given item number and you can format the ItemID field as a two column drop down with values from Products::ItemID in column one and values from Products::ItemDescription in column 2.


                          This is a simplified approach. You can enhance the solution from there by adding additional fields as well as by using more sophisticated tricks such as filtering your sales portal by a given date or range of dates to cut down on the needed scrolling or possibly using a horizontal portal trick to get multiple columns.

                          • 10. Re: Repeating Fields as Data Array?

                            Phil, I think your suggestion will work, but there is a slight error with my illustration of what was needed I thought I should share with you. (I do not work directly with that company, I just share a common building, etc).


                            What the company does is distribute newspaper publications. They basically employ "Carriers" to throw paper "Routes" and the "Pubs" consist of LA Times, Wall Street J, Daily News, etc...

                             Each "Carrier" throws various numbers of these "Pubs" according to a "Route" list and the total number is recorded each day. (Obviously the things in quotes are tables)


                            What I found out is the company pays each carrier the same regardless of which publication it is, so at this point in time, the Pubs table will serve a different function.

                            What is important now is having the ability to record the number of daily draws (Qty) each Carrier throws on a Route for two pay periods. (1-15th of each month and 16th-30th or 31st).

                            Also, they pay more for the Sunday papers, so they also need to know how many a carrier threw for every Sunday within each pay period.


                            EXAMPLE: Lets say this month, in the first pay period which is Saturday, May 1st and ends on Saturday, May 15th. Joe Blow Carrier delivers 25 papers on ROUTE 100 Monday thru Saturday and 50 papers on Sundays. (in this example May 2nd and May 9th are Sundays)

                            So he draws 325 daily papers and 100 Sunday papers for Route 100 in the first pay period and is paid accordingly, likewise for pay period two which is the 16th thru the 30th/31st, and then it starts over the next month.

                            (Joe Blow Carrier also delivers various quantity of papers on Route 110 and Route 120.)


                            Having said all of that... need to build a simple layout showing a carrier, all the routes assigned to them and a place to record how many pubs they delivered for each route in a given pay period. This is where the repeating fields came along, as it is simple to record numbers in linear fashion like a spreadsheet. How would you suggest data entry via portals for everything that requires a data entry?

                            • 11. Re: Repeating Fields as Data Array?

                              The devil is in the details and you've just added quite a few new ones! :smileywink:


                              Seems like you need a grid where one side is the day of the month and one side is the delivery route. At the intersection of these two variables, you'd record the number of pubilcations delivered. Make sense?


                              Since a given carrier will have far more delivery days than routes, I'd make Routes your columns and delivery dates the rows.


                              In filemaker 11, I'd do it this way:


                              Tables: Carriers, Routes, Deliveries



                              Carriers::CarrierID = Deliveries::CarrierID AND

                              Carriers::gPeriodStart < Deliveries:: DeliveryDate AND

                              Carriers::gPeriodEnd > Deliveries:: DeliveryDate  (Allow creation of related records for Deliveries.)


                              Routes::RouteID = Deliveries::RouteID


                              In Carriers, define a separate RouteID field, RouteID1, RouteID2 for each column of your grid.

                              Place 16 row portals to Deliveries on a carrier layout side by side, each with a diferent portal filter expression.

                              The column 1 expression: RouteID1 = Deliveries::RouteID


                              I now have a grid where I can record the total deliveries for each date with different routes separated in columns.

                              In earlier versions of Filemaker, I'd make separate table occurrences for each column and include the fields in the portal filter as part of each relationship:

                              Carriers::CarrierID = Deliveries1::CarrierID AND

                              Carriers::gPeriodStart < Deliveries1:: DeliveryDate AND

                              Carriers::gPeriodEnd > Deliveries1:: DeliveryDate  AND

                              Carriers::RouteID1 = Deliveries1::RouteID


                              There are variations on this theme that are possible.


                              • 12. Re: Repeating Fields as Data Array?

                                Yeah, sorry about that, wasn't given the full scope before either.


                                Right on, I make the adjustment and get back with you, thanks Phil

                                • 13. Re: Repeating Fields as Data Array?


                                  PhilModJunk wrote:

                                  In filemaker 11, I'd do it this way: ...


                                  Why don't you post a demo file showing this - I'd be curious to see what happens when:


                                  a) carrier skipped a day;


                                  b) user enters data into one of the filtered portals.

                                  • 14. Re: Repeating Fields as Data Array?



                                    I understand your design, but it can't be built with my "not very old" version of FM 10 Advanced. (you did say "in Filemaker 11"), lol.


                                    For anyone who didn't know - Portal Filtering is a new feature in v.11.


                                    Still, I'm convinced it can be done, as most anything you can create in MS Office, can be replicated in Filemaker, so I'm told.

                                    1 2 Previous Next