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.