Our Preferred 5 Star Hosting Provider - Award Winning FileMaker Pro Hosting
Results 1 to 5 of 5
  1. #1
    Forum Member
    Join Date
    Nov 2016
    Posts
    2
    Location
    tampa florida usa
    FileMaker Pro Software
    FileMaker 14 Advanced
    OS
    Windows
    Skill Level
    Intermediate
    Last Post
    11-06-2016
    Local Date
    04-30-2017
    Local Time
    02:36 PM
    Last Active
    03-20-2017

    How do I summarise and save Portal results in a Field?

    I would appreciate some help if anyone knows how to solve this.

    I have a database that has Shows where Salesmen sell products.

    There are many Shows and there are many Salesmen. Shows and Salesmen are joined by a table called Slips. Slips records their total sales each day - there is a Slip for every Salesman for every day of the Show.

    What I need to do is create something that gives, for each Show, who the salesmen were and how much they sold. But I need this data stored in a field in the Show table - not dynamically displayed as a portal and not as a graph.

    The reason I want the data is I want to display a single concatenated field which has the name of Shows plus the data of who attended last year and what their sales were.

    Because of the way this data will be displayed, I can’t use a portal or graph. The data will be displayed on a Gantt chart as a Task.

    It would look like this:

    ABC SHOW (Joe 34, Mary 52, Gill 26)

    where ABC SHOW is the name of an event coming up, and (Joe 34, Mary 52, Gill 26) are the salespeople and their sales the last time we did the show - a few months ago.

    As I say, I can get this data using a portal or a graph on the Show table - but I need to get the results into this single concatenated field. Somehow I need to save the data.

    Not sure if this can be done with Filemaker but I you have any ideas I would appreciate it.

  2. #2
    Senior Moderator - FMT Preferred Developer Consultant - Minnesota - Upper Midwest - USA - Global Ender's Avatar
    Join Date
    May 2004
    Posts
    3,702
    Location
    Minneapolis, MN
    FileMaker Pro Software
    FM14
    OS
    Mac
    Skill Level
    Developer
    Last Post
    02-15-2017
    Local Date
    04-30-2017
    Local Time
    01:36 PM
    Last Active
    04-26-2017
    See if the List() function will get you there.

    Otherwise, we can look at ways to build it with a scripted process, a custom function, or an ExecuteSQL() statement.

  3. #3
    Forum Member
    Join Date
    Nov 2016
    Posts
    2
    Location
    tampa florida usa
    FileMaker Pro Software
    FileMaker 14 Advanced
    OS
    Windows
    Skill Level
    Intermediate
    Last Post
    11-06-2016
    Local Date
    04-30-2017
    Local Time
    02:36 PM
    Last Active
    03-20-2017
    Quote Originally Posted by Ender View Post
    See if the List() function will get you there.
    Thanks for looking at this Ender. I have tried the List function and am possibly doing something wrong. If a Show had 3 salesmen (say IDs of 5; 8; 12) and each of them made sales on each of the three days the show lasted, then all I can make List produce for me is 9 rows with each ID repeated three times. I have been unable to make List only show the unique IDs, which could then be used in a calculation to get the total sales for each ID for that Show.

    There must be some built in capability to do this as if I ask FM to graph the sales for the show, it totals and displays the sales for each ID. Would there perhaps be some way you know of to capture those results used to generate the graph?

  4. #4
    Senior Moderator - FMT Preferred Developer Consultant - Minnesota - Upper Midwest - USA - Global Ender's Avatar
    Join Date
    May 2004
    Posts
    3,702
    Location
    Minneapolis, MN
    FileMaker Pro Software
    FM14
    OS
    Mac
    Skill Level
    Developer
    Last Post
    02-15-2017
    Local Date
    04-30-2017
    Local Time
    01:36 PM
    Last Active
    04-26-2017
    Not really. But there are other ways.

    A scripted process or a custom function could be used to march through the list and build the sub-totals. That's not too hard to work through.

    Another way is to use an SQL statement to pull the data and do the sub-totaling automatically. Getting the SQL statement right is the tricky part. If there's a syntax error, it's hard to tell where it is. You might also need one or two custom functions to parse the resulting table it returns (but these aren't hard to find.) The beauty of ExecuteSQL() is it can be used in anything that has a calc. And the result can be stored in a variable. This makes it great for feeding charts or storing summary data.


    Let's go through an example of an SQL statement that might work (with some adjustment for your field names.)

    Assuming the tables are:

    Show --< Slip >-- Salesperson


    And assuming the fields in Slip include:

    ShowID
    Sales Amount
    Salesperson Name (an unstored calc* pulling from Salesperson)
    Show Date (auto-entered Lookup from Show)


    The calc might look like this:

    ExecuteSQL("
    SELECT \"Salesperson Name\", SUM(\"Sales Amount\")
    FROM Slip
    WHERE \"Show Date\" >= ? AND ShowID = ?
    GROUP BY \"Salesperson Name\"
    ";//Other criteria can be added in the WHERE section with AND and OR clauses.
    "|"; //column separator
    "¶"; //row separator
    //For each ? in the query, include the criteria below separated by semicolons
    Date(month(get(currentdate)); day(get(currentdate)); year(get(currentdate)-1)); //date criteria for query
    Show::ShowID
    )


    SQL code is picky about the syntax and the field & table names. Names with spaces have to be quoted. Also, although relationships aren't needed on the graph, the table occurrence (TO) must exist on the graph.


    This should give you a result that looks something like this:

    Gill|26¶
    Joe|34¶
    Mary|52¶


    To turn that into a single comma separated list just takes a couple substitute() functions:

    Show Name & " (" & substitute(substitute($sqlResult; "|";" "); ¶; ", ") & ")"


    Or I suppose those separators could be used in the SQL statement itself if you don't need this result table for anything else.


    This is untested, so it might need some fiddling.

    *If speed is an issue, this may need to be a stored field in this table.
    Last edited by Ender; 11-04-2016 at 05:19 PM.

  5. #5
    Senior Moderator - FMT Preferred Developer Consultant - New York - Worldwide AHunter3's Avatar
    Join Date
    Aug 2006
    Posts
    12,133
    Location
    New York and Nassau County, NY, USA
    FileMaker Pro Software
    FileMaker Advanced/Developer 14, 13, 12, 11, 10, 9. 8.8, 8, 7, 6, 5.5 FileMaker Pro 8, 7, 6, 5.5, 5
    OS
    Windows & Mac
    Skill Level
    Developer
    Last Post
    04-27-2017
    Local Date
    04-30-2017
    Local Time
    02:36 PM
    Last Active
    04-30-2017
    You can also use ValueListItems.

    Define a value list of Slips::Salesperson ID showing only related values from Shows

    Now you can use ValueListItems (Get(FileName); "Your New Value LIst Name Here") from a Shows layout and it will return:

    34
    52
    26

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. One field combining the results of a portal
    By noodlenoggan in forum Calculations and Define Fields
    Replies: 2
    Last Post: 06-16-2012, 03:05 PM
  2. Replace Field Contents in a Filtered Portal - Variable Results
    By Jack Rodgers in forum FileMaker Pro 11
    Replies: 14
    Last Post: 12-18-2010, 06:09 AM
  3. Should I be using a portal to summarise data?
    By Varrenti in forum Portal & Relationships
    Replies: 0
    Last Post: 10-25-2009, 09:20 PM
  4. Portal Results From Partially Completed Field
    By jthawke in forum Portal & Relationships
    Replies: 6
    Last Post: 12-21-2008, 04:31 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 apps & solutions and interact with over 42,000+ FileMaker Developers from all over the world. 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.