Looking for some table setup advice

[griffman]

I have a data file that I get from our sales system; I have no control over this data, and it looks like this (skipping the extraneous fields) in one big table:

SaleDate, SaleTime, ProductName, Qty, NetRevenue, CustomerInfo, plus lots of other minor data

I get one row for each ProductName, even if a customer purchased multiple products on one transaction. As a result, there could be many rows for the same SaleDate and SaleTime. What I need to do is create an output that contains one row for each date, with all quantity and revenue for each product on that date. Assuming we have two products, FOO and BAR, I'm looking for something like this as the final output:

DATE           FOO-UNITS     FOO-REVENUE      BAR-UNITS    BAR-REVENUE
02-20-2018        10             100              7            84
02-21-2018         5              50             11           132
etc...

The only way I've been able to come up to do this so far is to add four fields per product to my database:

calcFooQuantity:      if ( ProductName = "FOO" ; Qty ; 0 )
calcFooTotalQuantity: Total of calcFooQuantity, running, restart when sorted by SaleDate

calcFooRevenue:       if ( ProductName = "FOO" ; NetRevenue ; 0 )
calcFooTotalRevenue:  Total of calcFooRevenue, running, restart when sorted by SaleDate

I then created a report sorted by SaleDate, and added the two running total fields, and it works as expected.

The problem is that this is absolutely unmanageable, because we have twelve products, so that's 48 fields…and adding a new product means four new fields. It also just feels kludgy as heck. Is there some way to use relationships and additional tables to make this simpler? My thought is a table for Products, obviously, but I fail when I start trying to figure out what that table looks like.

I can't use sub-summaries, I don't think, because I need the data to appear to be one row in the output—it's going to be exported to Excel via a script as the final step.

thanks!
-rob.

[Steve Martino]

What format is the data file?  And can you post a sample of that file?

[griffman]

It's a CSV file, but I'm not sure why that's important? I'm not having import issues, I'm trying to figure out the right database structure  once I have the data imported (which works fine). In any event, here's the header row and one data row, with disguised data.

date,newd,newt, ref, type, qty, productname, currency, total, subTotal, subTotalUSD, tax, referrer, source, campaign, firstName, lastName, company, email, phone, address1, address2, city, country, region, postal, coupons
1/13/18 8:44,01/13/2018,8:44,ORDERNUM12354-ABC, normal,1, FOO, EUR,12.58,12.58,13,0, , , , Joe, Anonymous, ACME Widget Co, [email protected], , 123 Main Street, , Cityscape, GR, ,15243,,

(Where FOO is the product sold.)

As noted, there's one row for each transaction, where a transaction is the purchase of a product—one order could generate five rows, if the customer bought five products.

thanks;
-rob.

[Lee Smith]

Hi Rob,

In case you are wondering how to post your file, take a look at this link, https://fmforums.com/guidelines/

HTH

Lee 

[griffman]

Lee:

I can't post my file—I don't want to spend the time to sanitize a bunch of the data rows to make examples, especially when every row is identical in structure to the others. The one row I provided is taken directly from my data file, with the data scrubbed, so that should show what my source data looks like.

-rob.

 

Edited June 23, 2018 by griffman

[griffman]

Steve:

My apologies on my tone above; I think I mis-read your intent. If you were looking for a sample file to play around with, I can provide one, but it will take a bit of time. There's a fair bit of confidential data that I need to disguise, yet still have the data file work.

I'll try to get that done this weekend.

regards;
-rob.

[Lee Smith]

See Step 8 of the link I posted above.

It explains how to post your file.

File »» Save a Copy As »» Type = Clone.

or

Sometimes it can be a accomplished by finding a limited number (2 or 30, remove confidential info, and export the found set.

Lee

[griffman]

Thanks Lee; I understand how to do it, I just need to take the time to sanitize a bunch of info and select a subset to make a usably-sized clone.

-rob.

[Steve Martino]

Seems odd to me to take a csv file from another program, into FileMaker,  just to consolidate it and export it out as excel.

I like using FileMaker for as many things as possible, by have you considered just importing directly into Excel? 

Or is there something you can do with the original data (don't know what software that is) to get the results you desire. 

[griffman]

The source is the third-party online sales system we use; it exports a CSV. It's obviously a transaction-oriented system, but to help run the business, I need a product-centric view. And I have no control over how it comes out—well, I have some, but it's always going to be transaction-centric. There's no ability to 

I'm using the data in FM for much more than this—sales by region, sales by day, revenue by month, by day, etc. It's my sales tracking system. However, I also have to create reports for my partner, and Excel is much better for me in terms of layout, charts, and data manipulation.

I can make it work using my brute force method, but I was hoping for some help creating a more elegant solution.

-rob.

[Steve Martino]

I'd still have to see a copy of the CSV file to help any further.  It needs to only have about 20 transactions (you can change the names to protect the innocent) and it needs to show records that have the problems you stated (multiple sales of same products, etc).

I would think you could do something with Group by on Excel and group by product name and date (you could even probably due that with a sub-summary report, then export).