Our Preferred 5 Star Hosting Provider - Award Wining FileMaker Pro Hosting
Page 1 of 2 12 LastLast
Results 1 to 10 of 13
  1. #1
    FREE Member
    Join Date
    Sep 2003
    Posts
    39
    Location
    Stockholm, Sweden
    FileMaker Version
    FM 11 Developer
    OS
    Mac
    Skill Level
    Intermediate
    Downloads
    0
    Uploads
    0

    Queries in Filemaker

    What is the filemaker way of building queries?

    Basically I want to create a report that summarizes amounts from different registers within specified account ranges and/or date intervals.

    I know how to build a query in SQL and Access but what is the Filemaker way to do this? Do I have to relate/import the data to one register and then create a report or is there a simpler way?
    Warning: This is an Old Thread
    This discussion is older than 420 days. information contained in it may no longer be current

  2. #2
    Senior Moderator - Consultant David Head's Avatar
    Join Date
    Apr 2001
    Posts
    2,833
    Location
    Sydney, NSW Australia
    FileMaker Version
    FM 11 Server
    OS
    Windows & Mac
    Skill Level
    Pro
    Downloads
    0
    Uploads
    0

    Re: Queries in Filemaker

    I think you need to give us more information about your current file structure. Are these "registers" different files or are they all records in the same file with a register field?

    Building a summary report is not too hard but I can't advise based on what I (don't) know about your structure.

  3. #3
    FREE Member
    Join Date
    Sep 2003
    Posts
    39
    Location
    Stockholm, Sweden
    FileMaker Version
    FM 11 Developer
    OS
    Mac
    Skill Level
    Intermediate
    Downloads
    0
    Uploads
    0

    Re: Queries in Filemaker

    Hello. My question was general but I can give an example from my current database. It's an accounting system that uses different registers/files for different transactions. For example, the accounts payables contains calculation fields and conditional value lists for accounts, amounts, VAT amounts and VAT accounts that depends on the selected supplier. (Other registers/files have other calculation fields and value lists, the idea is to make the system user-friendly for non-accountants.) I would like to build a query or report that summarizes the amounts from all my registers/files, grouped by account and period.

    I have considered using only one register/file for account and amount and relate these fields to my other registers/files but would I still be able to use different calculation fields and conditional fields then? I have also considered using an import script which works fine but surely Filemaker has a better solution than this to offer.

  4. #4
    Senior Moderator - Consultant David Head's Avatar
    Join Date
    Apr 2001
    Posts
    2,833
    Location
    Sydney, NSW Australia
    FileMaker Version
    FM 11 Server
    OS
    Windows & Mac
    Skill Level
    Pro
    Downloads
    0
    Uploads
    0

    Re: Queries in Filemaker

    To be truthful I don't really understand much more of your structure and what you are trying to report. I don't understand what a register is that you are referring to. It seems that you have different files for different transactions. But I don't know what types of transactions you are referring to and why they are in different files.

    It is difficult to comment or suggest a solution. Sorry. [img]/ubbthreads/images/graemlins/frown.gif[/img]

  5. #5
    FREE Member
    Join Date
    Sep 2003
    Posts
    89
    Downloads
    0
    Uploads
    0

    Re: Queries in Filemaker

    Hey,
    I saw your post about the consolidating registers the other day. Tom's response was very good if all you wish to do is look at your data on the screen. However, if your need is to report on data by account and date across several files (registers) that can get rather hairy.
    My advise is that you treat the registers as temporary/work files and go back to your import idea. Only difference is mark/flag the register records imported (allow viewing but not changing after import is done and corrections should be made by a reversing record in that register) and import only the new records each time.

    OR (always more than 1 way to do it. sorry)

    Import with update matching / add new records options checked in the import dialogue. This consolidating record is the best and easiest [img]/ubbthreads/images/graemlins/laugh.gif[/img] way to get the kind of report you desire.

    Accounting programs usually use this approach to store records for a detail account report. Many delete these register files (called work files) after posting to the consolidated transaction file for file size reasons. Allowing previously posted transactions to be changed is considered a serious violation of system integrity by many accountants. The audit trail of transactions is much cleaner if they can see mistakes in and mistakes out. Go figure?!~

    IMHO One of these ideas is your best solution to the reporting delima you have described.

    Best of Luck
    Tim
    [img]/ubbthreads/images/graemlins/wink.gif[/img] [img]/ubbthreads/images/graemlins/laugh.gif[/img]

  6. #6
    FREE Member
    Join Date
    Sep 2003
    Posts
    89
    Downloads
    0
    Uploads
    0

    Re: Queries in Filemaker

    BTW, side note: I assume that further along you are going to need to do some financial reporting (Income & Expenses, Assets - Liabilites = Net worth). Consider accumulating period totals in your account master file during the import in to the consolidated register. This will speed up your design and processing time on such reports if your data is already summarized.

    Just a thought. Hope it helps.
    Tim [img]/ubbthreads/images/graemlins/laugh.gif[/img] [img]/ubbthreads/images/graemlins/laugh.gif[/img]

  7. #7
    FREE Member
    Join Date
    Sep 2003
    Posts
    39
    Location
    Stockholm, Sweden
    FileMaker Version
    FM 11 Developer
    OS
    Mac
    Skill Level
    Intermediate
    Downloads
    0
    Uploads
    0

    Re: Queries in Filemaker

    Tim,

    Thanks for your reply! After many considerations I have also reached the conclusion that import is the best solution but being a newbie I wanted to get it confirmed by more experienced people.

    What I did worry about was having to re-import all data every time I post new records but your suggestion to flag imported records and only import records without a flag solves that issue.

    I still think a query function in Filemaker would be useful (for ad-hoc multi-table queries) but I guess that's an issue for the wish list.

    Once again, thanks for your help and my apologies to David Head for failing to clarify my issue.

  8. #8
    FREE Member
    Join Date
    Sep 2003
    Posts
    89
    Downloads
    0
    Uploads
    0

    Re: Queries in Filemaker

    nhjelmberg,
    Don't give up on what you want so easy!
    a query function in Filemaker would be useful (for ad-hoc multi-table queries)
    Please describe what you wish to be able to do. Give an example of the type of query and results you would expect.

    Tim [img]/ubbthreads/images/graemlins/smile.gif[/img]

  9. #9
    FREE Member
    Join Date
    Sep 2003
    Posts
    39
    Location
    Stockholm, Sweden
    FileMaker Version
    FM 11 Developer
    OS
    Mac
    Skill Level
    Intermediate
    Downloads
    0
    Uploads
    0

    Re: Queries in Filemaker

    Tim,

    Thanks for trying to help me. I think you have given me enough help with my application (thanks again). I'll try to explain why I miss ad-hoc queries.

    Say for example that you have one student table and one class table with a many-to-many relation (one student can take several classes and one class can have several students). You are interested in finding out how many female students that take the history class. In Access you can easily open a query window, select the tables, define the relation between them, select the fields and add search criteria (or simply write an SQL statement). This query can be saved and easily be used for a report. In Filemaker, however, you must actually create a layout or a register and do a find (or is there a better way?).

    To be provocative, it seems to me that Filemaker is less flexible than Access. If you can prove me wrong nobody would be happier than me. (But don't get me wrong, Filemaker does have many other advantages compared to Access.)

  10. #10
    FREE Member
    Join Date
    Apr 2001
    Posts
    1,429
    Downloads
    0
    Uploads
    0

    Re: Queries in Filemaker

    AFAIK -- FileMaker is probably less flexible in complicated things and queries. You can achieve a lot, but it must be programmed.
    But FM has because of different architecture another advantages -- speed is generally speaking the same with 1 or 100 users.
    In your case do the search on proper layout, create script with find and check restore find request. Then assign that script to button and you have "query".
    If you want to produce another, use "modify last find"; change it to your liking and save it as another query.
    For sure, some of those things are difficult to the beginners, but we always get there.
    Also FM doesn't have the flexibility of SQL, but that is different world.

    HTH

Page 1 of 2 12 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Execute SQL Select Queries on Filemaker Data
    By rdavis in forum ScriptMaker and Scripting
    Replies: 2
    Last Post: 09-29-2009, 08:32 AM
  2. ad hoc queries difficult
    By bingobat in forum Portal & Relationships
    Replies: 0
    Last Post: 09-23-2007, 04:26 PM
  3. Upgrading from v5 to v7 queries
    By eSBee in forum Installation and Upgrade Issues
    Replies: 4
    Last Post: 12-29-2005, 11:28 AM
  4. Upgrading from v5 to v7 queries
    By eSBee in forum The Ray Cologon Files
    Replies: 4
    Last Post: 12-29-2005, 11:28 AM
  5. Generate dynamic SQL Queries in FileMaker
    By D_Settles in forum Exporting and Importing
    Replies: 1
    Last Post: 02-23-2004, 11:55 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Welcome to the Original FileMaker Forum
With designated forums for almost every FileMaker topic...FileMaker Today is a free-to-join community where you can boost your FileMaker expertise, build better solutions and solve your technical challenges. If you're building FileMaker solutions, this is the place for you.
Follow FMT
We Are FMT
FMT is home of The World's leading FileMaker News Site, Worldwide Developer Directory, FileMaker Connect Community and so much more.