Too many relationships! Can this be simplified?

[Quartekoen]

Here's the story.

My company is moving over to a slotting system for our warehouse. Anywhere where a skid of a certain product can be put will be given a unique slot number. The number system works as follows:

Warehouse # - Row # - Bay # - Shelf

An item in warehouse 2, row 6, bay 15 (this means 15 bays down the row), and on the second shelf up will have the ID 2-06-15-B (Lowest shelf is on the floor (A). Highest possible shelf is 6 high (F)).

Some of the employees would like to have a graphic representation of the items in any given row. Here's the solution that I've come up with so far.

Imagine a grid. Each column is labeled with a number, as is each row. Each column label is a field, since (depending on the row) it could begin with different numbers (row 1 may start with 1. Row 8 may start with 27. It MUST be done this way because of the warehouse layout).

Currently, I have a portal showing a single record. It is based on the warehouse and row that was suggested by the user, then by the bay and shelf that is pre-designated for that portal. This way, the user enters any warehouse and row, and it will show whatever is in that location, even if the first bay in that row starts with a number other than 1.

The problem comes with making the portals. Currently, the most bays a row can have is 40, and the highest it can be is 6 shelves. With this, I would have to create 240 portals, each of which would have a unique relationship based on the bay and height. I can only hope that there is a better way. Any help would be greatly appreciated. Thanks.

[Fitch]

If it's just for viewing, maybe calculated CSS/table in a web viewer would work.

[Quartekoen]

Hmm. I haven't done anything yet with IWP or the like, but if it's a possible solution, I'll definitly look into it. Whatever it comes down to, it's got to be easier (and cleaner) than creating 240 relationships. Thanks!

[David Jondreau]

I've never done this, so I'm not sure, but I'm going out on a limb here and saying repeating fields may be the way to go.

Six repeaters per record with 40 repeats each, one record per row, if I'm reading your logic right.

...maybe 6 relationships, one for each shelf height?

You could load the repeaters via script or make them calc fields.

As I said before, I haven't done this, perhaps someone from our local Repeaters Society can chime in.

[Fitch]

Using the web viewer has nothing to do with IWP. The WV provides a layout-based calculation engine that is proving to be much more interesting than most of us understood when the feature first came out.

[Fenton]

Take a look at the PhotoManagement file that Genx has here:

http://www.fmforums.com/forum/showtopic.php?tid/184840/post/286111/hl//fromsearch/1/#286111

Could this not be extended to 6 rows high and 40 (or however many) portals wide (they're all the same relationship)?

[comment]

Perhaps I am missing something, but I don't see why this would take "240 portals, each of which would have a unique relationship". All I can see is one relationship to put a product into a slot, and another one to select which slots to show.

[Quartekoen]

Comment, let me clarify why at least I can't figure out how to do this with just 2 relationships, then maybe you can enlighten me

Suppose I use two repeating fields. One has 40 repetitions, and labels the columns (bays). The other has 6 repetitions and labels the rows (shelves). My problem is that I can't figure out how to set up the relationship so that it always shows only one slot, located at bay[n] and row[n], since these change for every bay and row.

My current setup (with only 1 relationship) is to show only records where:

findwarehouse = WAREHOUSE

AND findrow = ROW

AND col1 = BAY

AND A = SHELF

Where col1 is a number field with the beginning bay number in it, and A is a number field with the shelf number (this IS stored as a number, it is only displayed as a letter). If I create a repeating field (ColRep), then sure, I can change the relationship so that ColRep = BAY, but then it only uses the first value in that field.

Edited July 29, 2008 by Guest

[Quartekoen]

I've been playing with it, and it's working better than before. I have a single portal now showing all shelves for the first bay. Now I have a couple more questions. Using the repeating field, is it possible to have the portal show the rest of the bays, though only using one relationship? My current attempts with the repeating field in the relationship causes the portal to use all the repeating field's values, but vertically. Is it possible to have them fill horizontally as well, thus covering the rest of the bays?

Second, the portals do well to show all the records for a bay, but they list them from the top of the portal going down. I've sorted them to reverse the direction (since the bottom row is the starting point), but not all rows are 6 shelves tall, so the portal starts higher than the row itself. Is there any way to (without add-ons) to make the portal show records from the bottom up? Thanks

[comment]

I don't see what advantage repeating fields would bring here. I have a feeling the rows/columns thing is unnecessarily confusing the issue.

Let me take a slightly simpler example: say we have 6 boxes, and each box has 240 slots. That's 6 * 240 = 1,440 records in the Slots table. Each slot has a BoxID field, so if you choose to view the contents of box #2, that will cause slots 241 - 480 to show up in the portal.

The rest is but a matter of layout arrangement: if you want to view the 240 portal records in a grid of 6 rows x 40 columns, make the portal 6 rows high, duplicate it 40 times and set the initial row of each instance to 1, 7, 13, 19, and so on.

[Quartekoen]

While this would work in most situations, the problem here is that the slot numbers do not increment by a set amount. Instead of just going up by one each time, they may go up by one for 4 slots in a row (for the shelves), then the bay will increment and the shelf will go back to one. Also, because different rows have different shelf heights, I can't just tell every portal to start 'x' more than the last one (portal 1 might end at height 4 in this row, but it may end at height 6 in the next row). While I could definitely give each record an incremental ID field, the current method of doing this is already (and always will be) unique for each slot, so I'm not sure if it necessary (or good practice) to put a second unique ID in each record.

[comment]

The last thing first: having a MEANINGLESS unique ID for each record ***IS*** good practice. It will protect you from having to completely rework your solution when your management decides to change their numbering scheme.

As for the variations, it depends on how consistent they are (I can't make sense out of your description). You cannot tell portal instances to start at a constant offset relatively to each other anyway, it's all manual work. So the first portal could be 4 rows, the second 6 rows, and so on.

However, that's only good when all viewed groups have the same arrangement. If not, then I would simply mark certain slots are inaccessible and use conditional formatting to indicate so. This has the additional advantage of being able to calculate the row/column of each slot from the serial ID, instead of manual entry.