Jump to content
Salesforce and other SMB Solutions are coming soon. ×

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


JakeJames
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Edited by Ender
Link to comment
Share on other sites

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

Link to comment
Share on other sites

Wow!! Thanks so much for this Ender! I have been been playing with this and am generating some results. I know nothing of SQL and your little example here has given me the courage to jump in and have a go. I will need to work with this a bit and will let you know once I have it solved. Much appreciated!

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use