Solution required

AppSheet AppSheet Q&A
,
1

hi,

i have 4 departments in my unit, each department has several storage locations, that are numbered. The flow of material is from A department > B department > C Department > D Department.

when A department stores a particular batch 123 in say Storage location A1 which takes about 3 hrs to fill, i want this location to show unavailable for other members in the A department. here in this table i have status column showing “In Progress” and “Completed”

And when the material from batch 123 is taken from location A1 by B department, i want the location A1 to show Available only after B Department finishes clearing the location where in their table hey have column showing “In Progress” and “Completed”.

i could be contented by a way if i am able to insert a new column where i am able to display available locations for the user to select the appropriate available location for the next batch of material

How do i achieve this ???

2

It is important to think about the main goal of the app in real world terms and then build the data to mimic that.

It seems to me, based on your description, that the main goal of the app is to track the movement and activity of a BATCH. Batches are stored in different locations and departments take different actions on the batch, but it is the batch that is common throughout.

So, I would create a Batch table as my main table. Then have a table for Batch Movement and supporting tables for Departments and Storage Locations.

On the the Batch Movement table there would be columns for “From” and “To” to specify the locations from the Storage Locations table. Could be just “To” location but I like to keep both.

Then as each batch moves from department to department (in reality storage location to storage location owned by a department) its just a matter of adding a new Batch Movement record.

Or course, you would need to weave in the updates of batch status as well as storage location status, etc.

2 Likes
3

Hi I understand ur point that tracking batch would be the key…

But for my planning I need to know which storage locations are available, as certain locations hold more and certain less qty, hence I need to plan my batches according to Storage location available.

Hence my requirement as stated earlier

4

Oh, ok. Maybe I mis-understood. I took your post as a request for advice on a data design to allow you to manage the process you described.

But if you already have data tables created, please let us know, in a general sense, what they are and what data they hold related to your question, then we can help with ideas of how you can fit in the selection of the Storage Locations.

5

As of now I am yet to create an automated table, as I am not getting the expression to get this,

Currently my team manually enters which is available and which is filled in a separate sheet meant for only storage locations…

I want to automate this by when they enter the data in the batch sheet regarding storage location…that entry automatically shows in the storage location sheet

6

So let me summarize my understanding.

I assume that you have in an AppSheet app with a Batch table that is sourced from the batch sheet and a Storage Location table sourced from the storage location sheet.

When filling a tank, you have a Fill status column in the Storage Location table that shows “In Progress” and “Completed”. There is also a Department column to show who owns the location.

With a batch in location A1, when B department moves this batch into location B1, you want to AUTOMATICALLY show location A1 available ONLY once the location B1 shows “Completed”.

Is my understanding correct?

Assuming my understanding is correct, there are two problems:

  1. If your Fill status column only has “In Progress” and “Completed”, you don’t have a way to show that a location is available. This is easily solved by adding an “Empty” status.

  2. When marking location B1’s Fill status as completed, there isn’t a way to KNOW, in the app, where that batch was moved from. So you don’t know which location to mark as Empty. This is the main issue.

There a couple of easy ways to solve Problem 2

A) You could provide buttons on each storage location row in the App to explicitly set the status. For example, when B department is moving batch from A1 to B1, they tap a button on A1 indicating its empty in addition to tapping a button on B1 that the fill is complete.

B) When B department taps the button to indicate that fill of B1 is “In Progress” they are presented a short Form to indicate which location they are moving from (e.g. A1). You would add a From column in the storage location table to save this info. Now when the user indicates that B1’s Fill process is complete, the app will KNOW which location to mark as “Empty”.

How to get list of locations that are available for use?

Once you have solved Problem 2, then you can get the list of available locations with a simple expression:

SELECT(Storage Location([Location ID], AND([Department] = "A", [Fill Status] = "Empty"))

Note: How you get the value for the Department comparison will depend on how you have the app built, so you will likely need to modify this part of the expression to fit with your app.