Equipment inventory log

Nikki W

I am trying to create a workspace with multiple spreadsheets that will transfer rows back and forth from one to the other automatically. I have a sheet for inventory, one when the equipment is assigned to someone, and another to archive any movement. I have a form that they fill out when assigning the equipment out which places the information on the equipment assigned spreadsheet. Once the equipment is returned they fill out another form and that information is automated to be copied to the archive spreadsheet and moved to the inventory spreadsheet. I am having trouble automating the inventory spreadsheet to remove the equipment line when it is in use. I hope this makes sense. Thanks in advance for any and all help on making this happen before my go live date on Monday.

Michelle Choate 2

Do you happen to have Data Mesh? Because then you could capture all of this information all on one line.

Otherwise I would consider creating a duplicate column that will flag when something is seen more than once in the sheet (=IF(COUNTIF(Name:Name, Name@row) > 1, 1, 0) Something like that. This however, will flag both lines.

Here is where I would have another column that says what the transaction is (Be a multiple dropdown column, Two options "Assigned", "Returned"). Have these be hidden questions on the forms with a pre-filled out value, so it is captured every time.

Then create your automation to move row to the archive sheet based off of when Duplicate is checked, and the one condition, so long as Transaction Type is "Assigned".

That is what I can think of off the top of my head.

jmyzk_cloudsmart_jp

Hi @Nikki W

I recreated a solution from my previously built equipment management demo solution.

In managing the equipment assignment and returns, I've implemented a two-sheet system utilizing cross-sheet references instead of row-moving automation to optimize performance and update speed. This approach circumvents the delays typically associated with moving rows between sheets, ensuring more dynamic and real-time updates.

System Overview:

1. Inventory Sheet: Acts as the user interface where requests for assigning or returning equipment are initiated. It features URL links that direct users to specific forms.
2. Equipment Assigned Sheet: This is the record-keeping sheet that logs all form submissions related to equipment assignments and returns.

https://app.smartsheet.com/b/publish?EQBCT=6c9076e9f6ee44f09a9288ce70c71674 (Link to the published sheet. Note you can use the form Link in the sheet to update the information using the form. After the refresh, the information will be updated. You need to login to Smartsheet as the form uses the Created By information.)

https://app.smartsheet.com/b/publish?EQBCT=2cb301afd9774eceb0ac2eaa35c3de80

Key Process:

• Users interact with forms linked in the Inventory Sheet to either assign or return equipment. It's important to note that while the form URLs are hosted on the Inventory Sheet, the data entered into these forms does not populate this sheet directly.
• Instead, the forms submit data directly to the Equipment Assigned Sheet, where each transaction is recorded. This ensures all historical data about equipment status changes are centrally stored and managed on the second sheet.
• The Inventory Sheet then retrieves the relevant information from the latest row for each equipment ID on the Equipment Assigned Sheet using a set of formulas. This setup ensures that the Inventory Sheet always reflects the most current status of the equipment based on the most recent updates in the Equipment Assigned Sheet.

Detailed Formula Explanation:

Equipment Assigned Sheet Formula:

• Latest:
  • IF(RANKEQ([Record ID]@row, COLLECT([Record ID]:[Record ID], [Equipment ID]:[Equipment ID], [Equipment ID]@row), 0) = 1, 1)
  • Identifies the most recent record for each equipment ID, which is used to fetch the most current data for the inventory sheet.

Inventory Sheet Formulas:

• Assigned To:
  • JOIN(COLLECT({Equipment Assigned Sheet : Created BY}, {Equipment Assigned Sheet : Equipment ID}, [Equipment ID]@row, {Equipment Assigned Sheet : Latest}, 1))
  • Retrieves the creator of the latest record for each piece of equipment, indicating current custody.
• Status:
  • JOIN(COLLECT({Equipment Assigned Sheet : Status}, {Equipment Assigned Sheet : Equipment ID}, [Equipment ID]@row, {Equipment Assigned Sheet : Latest}, 1))
  • Pulls the latest status of the equipment to show whether it is assigned or available.
• Date Assigned & Due Date:
  • JOIN(COLLECT({Equipment Assigned Sheet : Date Assigned/Due Date}, {Equipment Assigned Sheet : Equipment ID}, [Equipment ID]@row, {Equipment Assigned Sheet : Latest}, 1))
  • Fetches the dates relevant to the latest assignment.
• Date Returned:
  • JOIN(COLLECT({Equipment Assigned Sheet : Date Returned}, {Equipment Assigned Sheet : Equipment ID}, [Equipment ID]@row, {Equipment Assigned Sheet : Latest}, 1))
  • Retrieves the return date if available.
• Request Type:
  • IF(OR(CONTAINS("Return", Status@row), ISBLANK(Status@row)), "Assign", IF(Status@row = "Assign", "Return", ""))
  • Determines the next action (assign or return) based on the equipment's current status.
• Request & Form to Use (Hidden):
  • IF([Request Type]@row = "Assign", [Assign Form URL]#, IF([Request Type]@row = "Return", [Return Form URL]#, ""))
  • Constructs and selects the URL for the appropriate form based on the next required action. The form URLs are stored in the Sheet Summary Fields.

Benefits:

This system ensures all updates are handled efficiently, with the Inventory Sheet always displaying up-to-date information based on the latest entries from the Equipment Assigned Sheet. This method minimizes manual data handling, reduces error, and ensures a high level of data integrity by centralizing data entry and retrieval processes.