Recreating a spreadsheet layout

[Craig EKI]

original title: Recreating a spreadsheet layout - daily sales of line items for customer, by product, arranged by day of month ascending across portal row, viewed by month

I have purchased a business (a commercial laundry) which previously used a separate spreadsheet for each customer, each month, to track sales of each item, items were in rows and columns the day of month. I am wanting to replicate this layout in FM, with the initial intent of getting having a form per customer per month which is transferred to FM for invoicing, and ultimately having the ability for staff to enter the data directly, removing the manual step and duplication of task.

I understand that there will need to be a record created for every line item for every day of the year, and the idea was to have a portal where the first field would be DayOfMonth(1), sorted by Month, with summary field to aggregate monthly sales for that line item across the portal.

I have done some FM writing years ago but could only aspire to the Developer moniker of "Rusty". Is this something which is relatively straight forward or am I dreaming?

 

Edited March 1, 2021 by Ocean West
edited title

[comment]

First of all, Filemaker is not a spreadsheet and recreating a spreadsheet layout is not necessarily the best way to enter and/or display the data.

Still, if you want to have the kind of layout you describe, I believe you could accomplish it without requiring "a record created for every line item for every day of the year" (which IIMHO would be a spectacularly bad arrangement to have). 

The attached demo shows one way you could look at it. For simplicity, I have used a table view; if you want to add labels for the days of month, you will need to use a list view instead (or at least disallow resizing and reordering columns in table view properties).

Note that this uses a repeating field to hold the daily quantities. This means your ability to report by day is limited. OTOH, it is probably the simplest structure that would allow this kind of layout.

 

 

MonthlyOrders.fmp12

[Craig EKI]

Thanks very much - this does the trick. Appreciate the prompt response.