Obtaining looked-up data based on a list stored in a field

[owangolama]

Here is my situation.

I have a 25x25 grid - let's call it a map. Think of it like a game board, like Battleship or perhaps an Excel spreadsheet.

I have a 9x9 view of this grid since it is not possible for a phone user to practically view 25x25, but 9x9 seems to work fine.

And I have the concept of Current_Location on the map, which is dynamic and changes based on other factors.

In TABLE1 I have records of all the "cells"  in the 25x25 map that hold information about the cell (for example, the color of the cell). There is other information stored here that includes, for example, whether you can "see" other cells from this particular location in the grid. So in the record for, say, cell B5, I have a field that houses a return-delimited list of the other cells that can be "seen" from this cell (B5 in this instance).

Another table, TABLE2 has records that are also have a field for Current_Location, and it tells me which cells (of the 25x25 grid) should be displayed in the 9x9 grid. Usually, the Current_Location is the middle cell in the 9x9 grid, but if the Current_Location is on the edge of the 25x25 grid, then the 9x9 view is adjusted accordingly and the Current_Location is shown on the edge of the 9x9 grid. The user can also change their orientation so they can view the map from a different direction. The way I store this information is in a field that simply lists (return-delimited) all the cell that should be shown in the 9x9 grid. So in our previous example of B5, the record for B5 has a field called Grid that is a list of the 81 cells that should be shown in the 9x9 grid.

For other reasons, I cannot combine these two tables even though they both are based on the concept of relating information to Current_Location. TABLE1 is actually not as simple as described, but I'm trying to limit the scope of my question.

So here is my question: I have 81 (sigh) objects on my layout for this 9x9 grid. I need to access information in TABLE1 for each of the cells that are represented in the 9x9 grid.

For example, let's again assume that my Current_Location is B5 and by using TABLE2 I know the names of all the cells shown in the 9x9 grid (they are stored in a list in a field in TABLE2). How do I then get information about each of those 81 cells out of TABLE1. I.e., each of the 81 cells should be colored according to information in TABLE1.

So, for the top-left cell in the 9x9 grid (which appears as the FIRST item in the list field in TABLE2), I need to be able to look up the color in TABLE1, and use it to drive Conditional Formatting of the cell. So if Color(B5)=3, then make the background color of the cell green. I can grab the cell name, and could easily write a script to figure out the color, but I need to *look up* the color to use it in Conditional Formatting.

I was trying to generate another field somehow that was an analogous list of 81 items that showed the color of each of the 81 cells (in the correct order), but 1) I couldn't figure out how to do that without running a script (which would take too long since these things are changing frequently and I really just need a data lookup); and 2) I think there must be a much cleverer way. I am still very new to join tables, but I think the answer is in that arena somewhere. The other idea I had was trying to write a Custom Function since I can easily grab the name of the cell, B5. But I don't know how to create such a function since it needs to access a particular table and it seems like functions don't really work that way.

Thanks in advance for any help!!

[OlgerDiekstra]

Why not create a layout with 9 button bars, each 9 buttons (to accommodate your 9x9 grid) and drive the values with a json variable? Json is essentially an array and you can create your 9x9 grid quite easily (without using a table). A button bar has the advantages that you can calculate the value it shows on the button (normal buttons can't though there are workarounds), and you can drive the conditional formatting easily through the json variable.

The json variable should have 81 subarrays, which can then each hold various properties, such as colour.

[comment]

This quite confusing and very difficult to follow. I picked these three sentences:

Quote

TABLE2 has records that are also have a field for Current_Location, and it tells me which cells (of the 25x25 grid) should be displayed in the 9x9 grid. 

The way I store this information is in a field that simply lists (return-delimited) all the cell that should be shown in the 9x9 grid.

How do I then get information about each of those 81 cells out of TABLE1.

Let's say you have a field in Table B that holds a return-delimited list of names. And that there is a Name field in Table A. If you define a relationship matching on these two fields, then the record in Table B will see all the records in table A whose name appears in the list as related - and can get their information. For example, a calculation of:

List ( Table A::Color )

would return a return-delimited list of 81 colors. How to use such list is another question and I am afraid I did not understand your description well enough to provide an answer.

--
P.S. I am not sure what is your overall purpose here, and I suspect there may be a better way to approach this.

 

[comment]

Going back to the beginning:

3 hours ago, owangolama said:

I have a 25x25 grid - let's call it a map. Think of it like a game board, like Battleship or perhaps an Excel spreadsheet.

I have a 9x9 view of this grid since it is not possible for a phone user to practically view 25x25, but 9x9 seems to work fine.

Have a look at the attached file, where I use a 5x5 window to look at a 16x16  grid. I don't think can it get any simpler than that. 

Another option would be to use 5 portals of 5 rows each, arranged side-by-side to form a grid, and filtered to show the corresponding records from the large grid. 

SmallWindowToLargeGrid.fmp12

[comment]

23 hours ago, comment said:

I don't think can it get any simpler than that. 

 I might have been wrong about that.

 

SmallWindowToLargeGridPortal.fmp12

 

 

 

 

Edited February 23, 2019 by comment

[owangolama]

Thanks to all for the comments/thoughts - I will have a look at these and see if I can make them work. I found a solution that uses ExecuteSQL that seems to be helping a lot.

[owangolama]

Thanks again for posting those files. They are similar (though more refined) than a layer I am using in my solution, so they will help since I can improve that particular layer.

I have still ended up using ExecuteSQL for some of the other functionality of the grid. I think it might be possible to simplify my solution further and perhaps do away with ExecuteSQL, but I'm not sure yet.