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