Re: Grid View for Particular Categories in a Datab...

Mike303 · 11-18-2023 02:43 PM

Hello!!

I built a basic inventory app for the inventory used in a biological research lab. In order to make the app work the way I need it to, I have all of the inventory in a single spreadsheet, which is used as the database in AppSheet. The app is organised by category: Bacteria, Yeast, Oligo, etc etc. When you click on a category, you can view all the items in that category.

Everything works perfectly, except for one thing. Certain inventory items are stored in test tubes, which are then stored in 20 x 20 grid boxes, which are then stored in different freezers. When viewing one of these items, it would be extremely beneficial to see a grid view of exactly where in the box the item is located. This is due to the need for the box to be kept below a certain temperature, so the faster an item can be retrieved from a box and returned to the freezer the better. In addition, it would be beneficial to see if a particular box has any empty slots, without physically removing it from the freezer. This would allow a researcher to quickly get a sample into a box in the freezer and easily add it to the inventory. Also, this "view" would only apply to about four out of 10 categories.

With traditional code, like Java or Python, I could make this happen by using a 2D array. However, I cannot figure out how to do this in AppSheet. I was thinking that "slices" could be used, but I honestly cannot figure how to get slices to do anything I want them to. Any help or suggestions would be greatly appreciated. Thanks in advance!!

Kabuliño

It occurs to me that you could add a drawing type column, in that column you could upload a photo of the rack (box) and the researcher could mark the place where the sample is located. I suppose you could also do something "similar" with an XY column

Mike303

The image suggestion sounds like a good option. Can you explain more about the "XY column"? Thanks!!

Mike303

One drawback to the image idea would be that there are hundreds of items in boxes. I should have also mentioned that there is already a system for how each test tube is placed. The inventory item has data showing which freezer it's in, which box it's in and where it's located in the box. For example, its location might be A15, or column A, row 15. The issue with the current format is that it's hard to visualise where the test tube is located and I can't see any open spaces in a particular box.

Suvrutt_Gurjar

It maybe or may not be possible . However could you share some expected image as to how the grid should look and with what parameters ( or two columns) in AppSheet. If you could describe something like below, it may help to get a better idea

So selection options for the user could be Row enum, Column enum and Item name. Then the plotting could be in another table for this particular category.

There is of course no built in grid option, but possibly one could be built with some workarounds. But one may wish to be aware that the solution may not be very easily scalable and maintainable.

Mike303

So it would look similar to the image below. You would have the freezer location, in this case the -80C freezer, the box the sample is in, "Angela's Box", and a view of the item, "D6" for example. But you would also be able to see the other items in the box and any open slots.

Suvrutt_Gurjar

Got it. Thank you. I think in current setup it may not be very easily feasible in AppSheet. If there were only one grid ( for one location) , then possibly , it could have been implemented, of course that too with some complex workarounds.

However the fact that there could be multiple freezer locations , as you have indicated, will make the implementation that much more complex and infeasible.

Kabuliño

something like this...

https://www.youtube.com/watch?v=ZRq-QDG0NUM&t=2380s

TeeSee1

One solution can be to use SVG.

Here is a sample implementation.

Grid Boxes table with a child table that contains cell rows. The cell row contains a ref to the item it stores.

Click the image to enlarge it.

The item has a copy of the image of the grid box that it is housed in.

This is the code I had ChatGPT generate, modified and used in a VC of type image (you can search the community for SVG usage tips).

CONCATENATE("data&colon;image/svg+xml;utf8,
<svg width=""700"" height=""700"" xmlns=""http://www.w3.org/2000/svg"">
    <!-- Draw the grid lines -->
    <rect width=""100%"" height=""100%"" fill=""white"" />
    <line x1=""25%"" y1=""0"" x2=""25%"" y2=""100%"" stroke=""black"" stroke-width=""2"" />
    <line x1=""50%"" y1=""0"" x2=""50%"" y2=""100%"" stroke=""black"" stroke-width=""2"" />
    <line x1=""75%"" y1=""0"" x2=""75%"" y2=""100%"" stroke=""black"" stroke-width=""2"" />
    <line x1=""0"" y1=""25%"" x2=""100%"" y2=""25%"" stroke=""black"" stroke-width=""2"" />
    <line x1=""0"" y1=""50%"" x2=""100%"" y2=""50%"" stroke=""black"" stroke-width=""2"" />
    <line x1=""0"" y1=""75%"" x2=""100%"" y2=""75%"" stroke=""black"" stroke-width=""2"" />

    <!-- Add black lines around the outer edges of the grid -->
    <rect x=""0"" y=""0"" width=""100%"" height=""100%"" fill=""transparent"" stroke=""black"" stroke-width=""2"" />

    <!-- Add text with cell coordinates -->
    <text x=""5%"" y=""5%"" fill=""black"" font-size=""12"" text-anchor=""start"" alignment-baseline=""middle"">A,0</text>
    <text x=""30%"" y=""5%"" fill=""black"" font-size=""12"" text-anchor=""start"" alignment-baseline=""middle"">A,1</text>
    <text x=""55%"" y=""5%"" fill=""black"" font-size=""12"" text-anchor=""start"" alignment-baseline=""middle"">A,2</text>
    <text x=""80%"" y=""5%"" fill=""black"" font-size=""12"" text-anchor=""start"" alignment-baseline=""middle"">A,3</text>

    <text x=""5%"" y=""30%"" fill=""black"" font-size=""12"" text-anchor=""start"" alignment-baseline=""middle"">B,0</text>
    <text x=""30%"" y=""30%"" fill=""black"" font-size=""12"" text-anchor=""start"" alignment-baseline=""middle"">B,1</text>
    <text x=""55%"" y=""30%"" fill=""black"" font-size=""12"" text-anchor=""start"" alignment-baseline=""middle"">B,2</text>
    <text x=""80%"" y=""30%"" fill=""black"" font-size=""12"" text-anchor=""start"" alignment-baseline=""middle"">B,3</text>

    <text x=""5%"" y=""55%"" fill=""black"" font-size=""12"" text-anchor=""start"" alignment-baseline=""middle"">C,0</text>
    <text x=""30%"" y=""55%"" fill=""black"" font-size=""12"" text-anchor=""start"" alignment-baseline=""middle"">C,1</text>
    <text x=""55%"" y=""55%"" fill=""black"" font-size=""12"" text-anchor=""start"" alignment-baseline=""middle"">C,2</text>
    <text x=""80%"" y=""55%"" fill=""black"" font-size=""12"" text-anchor=""start"" alignment-baseline=""middle"">C,3</text>

    <text x=""5%"" y=""80%"" fill=""black"" font-size=""12"" text-anchor=""start"" alignment-baseline=""middle"">D,0</text>
    <text x=""30%"" y=""80%"" fill=""black"" font-size=""12"" text-anchor=""start"" alignment-baseline=""middle"">D,1</text>
    <text x=""55%"" y=""80%"" fill=""black"" font-size=""12"" text-anchor=""start"" alignment-baseline=""middle"">D,2</text>
    <text x=""80%"" y=""80%"" fill=""black"" font-size=""12"" text-anchor=""start"" alignment-baseline=""middle"">D,3</text>

    <!-- Add text with the concatenation of y and x coordinates at the center of each cell -->
    <text x=""12.5%"" y=""15%"" fill=""black"" font-size=""12"" text-anchor=""middle"" alignment-baseline=""middle"">",LOOKUP(INDEX(SELECT(gb_cells[item],AND([gb_id]=[_THISROW],[cell]="A1")),1),"items","iten_id","item_name"),"</text>
    <text x=""37.5%"" y=""15%"" fill=""black"" font-size=""12"" text-anchor=""middle"" alignment-baseline=""middle"">",LOOKUP(INDEX(SELECT(gb_cells[item],AND([gb_id]=[_THISROW],[cell]="A2")),1),"items","iten_id","item_name"),"</text>
    <text x=""62.5%"" y=""15%"" fill=""black"" font-size=""12"" text-anchor=""middle"" alignment-baseline=""middle"">A2</text>
    <text x=""87.5%"" y=""15%"" fill=""black"" font-size=""12"" text-anchor=""middle"" alignment-baseline=""middle"">A3</text>

    <text x=""12.5%"" y=""40%"" fill=""black"" font-size=""12"" text-anchor=""middle"" alignment-baseline=""middle"">",LOOKUP(INDEX(SELECT(gb_cells[item],AND([gb_id]=[_THISROW],[cell]="B1")),1),"items","iten_id","item_name"),"</text>
    <text x=""37.5%"" y=""40%"" fill=""black"" font-size=""12"" text-anchor=""middle"" alignment-baseline=""middle"">B1</text>
    <text x=""62.5%"" y=""40%"" fill=""black"" font-size=""12"" text-anchor=""middle"" alignment-baseline=""middle"">B2</text>
    <text x=""87.5%"" y=""40%"" fill=""black"" font-size=""12"" text-anchor=""middle"" alignment-baseline=""middle"">B3</text>

    <text x=""12.5%"" y=""65%"" fill=""black"" font-size=""12"" text-anchor=""middle"" alignment-baseline=""middle"">C0</text>
    <text x=""37.5%"" y=""65%"" fill=""black"" font-size=""12"" text-anchor=""middle"" alignment-baseline=""middle"">C1</text>
    <text x=""62.5%"" y=""65%"" fill=""black"" font-size=""12"" text-anchor=""middle"" alignment-baseline=""middle"">C2</text>
    <text x=""87.5%"" y=""65%"" fill=""black"" font-size=""12"" text-anchor=""middle"" alignment-baseline=""middle"">C3</text>

    <text x=""12.5%"" y=""90%"" fill=""black"" font-size=""12"" text-anchor=""middle"" alignment-baseline=""middle"">D0</text>
    <text x=""37.5%"" y=""90%"" fill=""black"" font-size=""12"" text-anchor=""middle"" alignment-baseline=""middle"">D1</text>
    <text x=""62.5%"" y=""90%"" fill=""black"" font-size=""12"" text-anchor=""middle"" alignment-baseline=""middle"">D2</text>
    <text x=""87.5%"" y=""90%"" fill=""black"" font-size=""12"" text-anchor=""middle"" alignment-baseline=""middle"">D3</text>
  </svg>
")

LOOKUP(INDEX(SELECT(gb_cells[item],AND([gb_id]=[_THISROW],[cell]="A1")),1) needs to be modified to fit your table structure at the least.

I am no expert at SVG. But if you decide to use it and have questions, you can certainly ask them here for guidance from experts.

Mike303

This looks like it could definitely be an option. The max box (grid) size we use is 20x20 and everyone uses the desktop version, so I think it could work.

I'm still fairly new to using AppSheet, so I'm not entirely sure how to implement this. My current database has a row for "Location", which is a freezer, a row for "Sub-location", which is a box, and row for "Location Details", which is the grid reference (e.g. A4). All of which work with the code you've provided.

However, I'm not sure where this code should be entered in AppSheet. I've included a screenshot of how the app is set up in desktop view. Thanks again everyone!!

Kabuliño

Could you give us a list of all the box sizes you use?

In the boxes always use letters to define the rows and numbers for the columns?

Mike303

Sure, all the boxes are the same and yes, the rows are letters and the columns are numbers.

Kabuliño

Look, this is what I achieved

if you want, send me your email by private message and I'll share the app with you so you can copy it

Mike303

That looks amazing!!

Suvrutt_Gurjar

Nice one @Kabuliño

Suvrutt_Gurjar

That is a good POC @TeeSee1 . I had also toyed with the idea of SVG and even long text column with HTML option. That is why had shared a grid image in earlier post. However when @Mike303 posted the grid, it seems to be a large grid ( 99 cells 11 X9).

At times, I have experienced the expression editor getting saturated when a very large expression is composed. (It errors out with a message something like " Your expression could not be validated, we will check it again" )

Also in SVG image a very large grid cannot be accommodated as image necessarily remain restricted to certain pixel size in AppSheet ( I think 600 * 600 pixels) So each cell may become very small for human eyes.

A large grid is technically possible with HTML long text column but again formatting options are limited to highlight a particular cell with different color etc.

Finally after composing the grid, placing the item in teh cell will call for additional logic at each cell level that may again overshoot the expression limit and anyway become a very long expression.

All in all, just thought of sharing the possible issues, I thought and mentioned it may be technically feasible but not maintainable with large grids. Small grids of course it will work.

Please feel free to add if you have different ideas on handling large grids. I might have obviously missed some points.

TeeSee1

Thanks @Suvrutt_Gurjar for your info. I have had virtually zero experience with SVGs till now. Yes I am already getting the 'cannot evaluate' error but just ignored it and still works.

However your other points probably invalidate this solution for the Q&A author's case.

At this point, I do not have any other ideas to handle large sized grids within AppSheet. Graphics is not one of its fortes...

Suvrutt_Gurjar

No worries. You are welcome @TeeSee1 . In AppSheet workarounds are possible. The more the options /suggestions we all post the better.

Other possibilities I toyed with could be using a table of fixed say 9 rows with 11 virtual columns to represent columns in the grid and then dynamically placing the items in the cells when a particular Item is selected. Then present this grid table as a related table view. However it sounds that any such large grid will look better in the desktop view.

I will dig deeper with that concept and post back if feasible -meaning if the solution is decently maintainable and not sync time expensive. You also please feel free to add any ideas you may have.

Kabuliño

@TeeSee1 @Suvrutt_Gurjar
I hadn't read your answers, I also tried with an SVG haha
If I had received notification of your answers, I probably wouldn't have tried it, here is my result

Suvrutt_Gurjar

Thank you for sharing @Kabuliño . Your implementation looks good. As expected in summary and detail view, the image is illegible. However if one gives an extra keystroke at the image, the expanded image looks good. Again this implementation seems to be better suited for desktop environment. Thank you very much for sharing.

Grid View for Particular Categories in a Database