Jump to content

Welcome the ORIGINAL FileMaker Community

Take a moment to join us, no noise, all FileMaker...We Promise

ndveitch

Layout for multiple rows and columns from excel

Recommended Posts

ndveitch

Im not sure if that would be the right heading, but I am looking for the best way to produce a layout for my client which shows the number of orders placed by product on the Y-Axis and which branch ordered the products on the X-Axis. In Excel it is fairly simple where the products would be placed in the first column running down, then that column frozen, then the branch names across the top of the worksheet and frozen. Then my client just scrolls through the worksheet as she needs.

 

I have been thinking about using portals, but seeing as I have roughly 25 products and 250 branches at the moment for this sample test, im not 100% sure of how to build it for scalability.

 

Any ideas are always greatly appreciated.

Share this post


Link to post
Share on other sites
eos

Consider using a Web Viewer. Collect your data in list variables and format them as HTML table; maybe use a JavaScript library and a plug-in to offer convenience functions like column sorting etc.

 

IMO the most flexible approach, because the only layout element you need to specify is a Web Viewer, while its contents is determined dynamically by your calculation(s). With FM12 and newer, this is a natural fit for ExecuteSQL(); you're on 11, alas …

 

Caveat: this approach is more complicated should your client need editing capability (which I didn't gather from your description).

 

All native options require you to prepare the layout elements (portals, repeating fields etc.) beforehand; not only can that get very tedious, you also have no way to adapt that arrangement to the current data configuration. (FM14 with programmatic access to all layout CCS attributes, anyone …?)

Share this post


Link to post
Share on other sites
AHunter3

Alternatively, you can do it natively in FileMaker using cascading scrolling relationships to generate a grid, with horizontal as well as vertical record differentiation.

Share this post


Link to post
Share on other sites
Feirefiz

Very cool.

Share this post


Link to post
Share on other sites
ndveitch

Thank-you so much for the advice it is always greatly appreciated.

 

On a side note, I didnt understand why EOS mentioned FM11 and then I noticed that in the side bar profile it says that I work in FM11 when it should be FM12. I checked my profile and there it says FM12 so not to sure why in the side bar it says FM11 :confused:

Share this post


Link to post
Share on other sites
Feirefiz

Yeah, what's shown in the sidebar is off by two (?) from what's shown in the profile. (I changed mine so the sidebar shows correctly and the profile wrongly.)

Share this post


Link to post
Share on other sites
ndveitch

Cool Feirefiz, thanx :)

Share this post


Link to post
Share on other sites
ndveitch

Hi Hunter, As always thank-you for the advice. I followed what you said on the link as my knowledge of web viewers is very limited. I have been able to get the scrolling through the records working, but the vertical scrolling is proving to be tricky. Im sure I have gone about this the wrong way. For my case, the branches can order a product from a list. Then my client would get that list and approve full or approve partial or decline out right the order on a product by product basis per branch. At the moment there are roughly 600 branches and about 150 active products. She also wants to be able to see a report by branch, region and product as well as the last 3 months of orders.

 

Following your post on the cascading scrolling relationships, I was able to get it working for a small sample that I did. I have 5 portals set and the scrolling works nicely, as my records are based off the branch codes. The major issue that I am having, is that i am not to sure how to get the products into the system in an easy way. The way I got it working was to create a field for every product that would store the order amount for that product and then placed that in the portal's for the scrolling. This would be ok if it was only 1 or 2 products, but I have currently 150 products and when I include the approve/decline options, summary of those fields, plus a way to get the product info (pricing, stock quantity etc) from the products table, I am looking at roughly 10 fields per product item. That would mean i would have over 1000 fields just to display this order like an excel page.

 

I was also looking at using the branches instead of the products, but with there being roughly 600 branches, I decided to go with the Products. Im sure there is an easier way, I'm just unsure of how to do it.

Share this post


Link to post
Share on other sites
ndveitch

Ok, this is driving me insane. What am I missing here??? Im sure it is not this complicated. I have one excel spreadsheet that has data per product ordered by each branch. So in my mind that should go into a join table between branch and product but for some reason I have hit a brick wall.

In Excel it looks like this:

.................Branch 1 | Branch 2 | Branch 3 ...

Product 1 ..........5 | .........3 ..... |.......3

Product 2 ..........2 | .........4...... |.......5

...

 

and the user can scroll vertically across the 600 odd branches and horizontally through the 120+ products. The only way I have been able to get this working is by creating a field for every product and then the record is based off the branch code. But the user needs to approve or decline orders based on the stock on hand in the products table. Also they need to see the minimum quantity of each product which is updated daily via a URL link to their suppliers DB, which is working perfectly.

 

I have setup portals to display 5 of the branch records at a time and so far on my sample data this is working nicely, but if I continue down this rabbit hole I am sure I am going to be in a world of hurt, as each product has a field for the code, name, order amount and sum of, approve amount and sum of, declined amount and sum of, min and on hand qty and product code. This would mean that my table could potentially have over 1000 fields, and then I would need to do sorting, finds, charting etc, plus move the order to a history table for a 3 month report.

 

I must say that I have a way of trying to re-invent the wheel, so maybe I am thinking to much about this, as it cant be that complex. It's just that I am going crazy trying to work this one out, and walking away for a breather isn't helping either :(

Share this post


Link to post
Share on other sites
AHunter3

This makes no sense as a strategy:

The way I got it working was to create a field for every product that would store the order amount for that product and then placed that in the portal's for the scrolling

 

Do you literally mean a separate FIELD for each product? That's just wrong.

 

There should be one RECORD for each product. Each record should contain ONE field for the Product ID.

Share this post


Link to post
Share on other sites
ndveitch

Im not sure what was going on in my head when I was trying to sort this out. I think I got confused when I saw the excel grid and didnt think of it properly. After bashing my head against the table many times I took a step back, and then face palmed at how i had gone about it.

Share this post


Link to post
Share on other sites
ndveitch

Me again, I think I might have misunderstood the post, cascading scrolling relationships. I was able to get my solution working with 5 portals and all was going well until I started to work on different views for the data. I ended up making new self join TO's per view that I needed, for example, to view the products ordered per branch would be one set of 5 TO's, the view for branch orders per product is another set of 5 TO's, to get a 3 month comparison is 5 more TO's, to get the sum totals for the products ordered is yet another 5 TO's.

 

Now I am going to also be importing external employee sales into the mix. The theory would be similar to the branch order import, as it is just employee product sales and not branch product sales, but my fear is that by the end of it I am going to have possibly another 20 TO's, just to get the different views working. In a post I saw, Too Many Table Occurrences, Weetbicks mentions that there is no real limit to the TO's you can have and at the moment the only real issue I am having is that my list of Table choices when assigning a field is now pretty long.

Share this post


Link to post
Share on other sites
AHunter3

At this point I'm flying in the dark as far as what you've actually set up.

 

Your various TOs may make perfect sense or they may be more than you need for what you're using them for.

Share this post


Link to post
Share on other sites
ndveitch

What I have done is create an order table with 5 global fields for the scrolling, and 2 fields to store a number for each of the branches and for each of the products. These are set when I import the data from the excel spreadsheet. Then I did 2 sets of 5 self joins to the order table, 1 set for branches and 1 for products linking up the globals to the corresponding record numbers. When I put my layout together, I did one layout to display the products horizontally and the branches vertically, and another layout to show the inverse.

 

Then when it came time to work out totals for the various columns I had to make more TO's with slightly different relationships setups to show the data required. I have been able to get this working, but after all the different views and calculations I have needed to do I am sitting at roughly 30 TO's for these reports, and now I need to add in another lot of data, employee sales that are not related to the branches yet use the same products.

 

I started following the same method I have been using, and it is working. I just wanted to make sure that I wasn't going to run into any problems with having so many TO's joined to the same table?

Share this post


Link to post
Share on other sites
haniya11

on the first sheet, highlight the item then right click the mouse. select copy, flip the page onto record 2 then right click the mouse again and select paste. You can replicate this as many times as you wish. Then all you do is rename the sheets at the bottom of the page

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×