Jump to content
View in the app

A better way to browse. Learn more.

FMForums.com

A full-screen app on your home screen with push notifications, badges and more.

To install this app on iOS and iPadOS
  1. Tap the Share icon in Safari
  2. Scroll the menu and tap Add to Home Screen.
  3. Tap Add in the top-right corner.
To install this app on Android
  1. Tap the 3-dot menu (⋮) in the top-right corner of the browser.
  2. Tap Add to Home screen or Install app.
  3. Confirm by tapping Install.

Subtotals by category/month to Excel

Featured Replies

  • Newbies

I asked a similar question in the AppleScript forum, but realized that the solution doesn't need to be AppleScript if...it doesn't need to be. Perhaps you can think of a better way to accomplish my goal.

I have a FileMaker/Excel combination which is used for expense tracking and budget planning. (Think a bare-bones Quicken with some very specific customized reports and uses.) FM tracks the expenditures for different bank accounts and categories, and summary of these is brought over into the (existing) Excel spreadsheet to track performance and plan budgetting.

The Excel file has a sheet called "Incoming Values" with columns for each month, and ~65 rows for all the account/category combinations. (Other sheets draw off this 'raw data' sheet to perform the necessary calculations and such.) The desire is to find the sum of all expenditures (records) in an FM table for each account/category/year/month combination, and shove them into the appropriate cell in this sheet (including putting a '0' into the cell if no such records exist).

Ages ago this used to be done with Publish/Subscribe technology. Then (when Apple killed that) I hacked together a solution with AppleScript, which performs a find for each account/category/year/month combination, finds the sum, and shoves the data into a cell. For each of the over 780 combinations for each year. At 0.5-1 second per, this is slow. Combine the slowness with the fact that I'm having trouble getting the AppleScript to work with FM7 (I'm jumping straight from FM2.1 to FM7 smile.gif and the end result is me, here, asking you for a better way to accomplish the end desire.

So, in short: what's the best/fastest way to find subtotal information for each unique combination of set account, category, month, and year fields (including 0 values for combinations which are defined but without any records), and put these subtotal values into an existing Excel spreadsheet?

Thanks in advance for any help; I can show a (sanitized) version of the spreadsheet, filemaker file, and/or existing AppleScript if it helps.

Create an account or sign in to comment

Important Information

By using this site, you agree to our Terms of Use.

Account

Navigation

Search

Configure browser push notifications

Chrome (Android)
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
Chrome (Desktop)
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.