4 Replies Latest reply on Mar 28, 2011 1:25 PM by philmodjunk

    Newbie question - is this possible?



      Newbie question - is this possible?


      I am new to developing databases (it's been 15 years since I've worked with Filemaker and I was doing mostly basic stuff at that point). I am trying to figure out if the following is possible.

      I am trying to make a database that would streamline the following process: Every month, the retail manager uses an excel spreadsheet to note which products should be hung from a pegboard hanger (in a retail store). Sometimes they want four different products hung from one hanger in a certain priority (ie., the red product goes first, then the blue product behind it, etc) and sometimes they just want to hang four of the same product on one hanger.

      The retail manager has traditionally used a spreadsheet and one cell has represented one pegboard hanger. If they want to show four different products, they just list them in the one cell. There are different spreadsheets for different store types - but they all have these cells with up to four products listed.

      The frustration for the retail manager is having to do this manually in an excel spreadsheet with multiple sheets and cells and products...cutting and pasting SKUs, product names, prices, etc.

      I keep thinking that if I can assign each product (there are only about 200) a record number (in a field) with all its corresponding information (SKU, price, name, etc) then the retail manager could do a spreadsheet and just imput the record number (she'd have a master list) in excel and I could take that excel document and based on her record numbers, automatically fill in the rest of the information (for instance, in her excel sheet each cell would just say "2" or "132" instead of the name/info.

      At the very least, it sounds like I'm going to be using two databases - one with all the info including a record number, and then the database that will come from the excel document each month using just numbers. I know I can set up a relational database that matches based on record number. I think where I get hung up is trying to figure out how to show it in a layout so that it mimics the spreadsheet - so when she wants just one product hung multiple times from one hanger it only shows one record - but then when she wants four different products it will show the four. I've thought of using the layout for labels, so I could set up multiple "cells" on one page, to mimic what they are used to seeing in an excel spreadsheet. But then I can't figure out how I can tell it to just show one record on one "label" and four records on the next. I hope I'm making sense.

      The easiest solution would be to just have the retail manager list the same record number four times if they want to use all of the same product (since I'm assuming that four is the most they will fit on a hanger, and so some hangers will have four separate products). However, the stores are used to seeing only one product SKU in cell and they know it means to just hang as many of that one product as will fit on the hanger. I don't want the end result from Filemaker to have to list the same product four separate times (especially since the people at the store are very literal - so if we list a product four times and the products are large and only two will fit on the hanger - store employees will try to fit four on one hanger, or be upset that they've been told to hang four and it will only fit two).

      I will attach a screenshot of a couple of the cells in the original excel spreadsheet. I need the filemaker layout to look something like this - but with the addition of a graphic next to each SKU - when I'm done.

      I'm sorry - this is so long; just not sure how else to explain it completely.

      Thank you!


        • 1. Re: Newbie question - is this possible?

          Do you recall how to set up portals?

          Each "hanger" could be represented by a portal. If you hang just one product, enter data in one row of the portal. If more, add rows of data as needed. You can certainly use FileMaker 11 to have multiple portals to get columns of data like this. You can use 4 different reltationships for each portal, or you might be able to use one relationship with 4 different portal filters and auto-enter calculations that use the portal filter information to auto-enter data so that data entered in a portal doesn't mysteriously vanish from the portal because it doesn't have data specified by the filter.

          That's just a general description of capabilities. I suggest first thinking of these 4 columns shown in your screen shot as 4 invoices where the items listed for the hanger are the line item records to be invoiced. Take a look at the invoices starter solution that comes with FileMaker 11 or download this much simpler demo file create by another person who goes by the avatar "Comment" to see how you'd set this up for one hanger: 


          Once you get this working for one hanger, you can take on the challenge of getting it to work for more than one in the same layout.

          • 2. Re: Newbie question - is this possible?

            Ooh, thanks. I am checking that out while trying to wrap my mind around what you're saying. I might have to come back with questions. Thank you so much!!

            • 3. Re: Newbie question - is this possible?

              So I think I am understanding the portal solution and treating it like an invoice situation with products.

              Here's where I'm getting hung up now. If I give the client a spreadsheet (see attached) and have her fill in record numbers that she wants (as shown), how do I get this into the database? I just can't seem to wrap my head around getting that data into a workable layout/database or whatever.

              Is this too over my head perhaps? :)


              • 4. Re: Newbie question - is this possible?

                Why not have the user enter the data directly in to the database?

                It's certainly possible to set up a series of import scripts that import all this data, but why go to all that trouble when you can host the database over the local network or even the internet and they can then type in the data directly?