A key checkout log in SharePoint

Short answer: use two SharePoint lists. Keys holds one row for each key tag. Key log holds one row each time a key is taken, with who took it, when it went out and when it came back. A view of the log that shows rows with Status 1 Out lists the keys that are out now. This replaces the clipboard sign-out sheet, which gets lost and cannot be searched.

One honest point: keys are physical security. A SharePoint list is a record of who took a key. It is not a lock. It cannot stop anyone from taking a key, and it cannot tell you the key is really back on the hook. Read the "Honest limits" section before you build.

Exact names may differ on your screen. Microsoft's help pages are linked at the end.

What a filled-in key log looks like

Here is a sample of the Key log after a few days. Row numbers are only for reference.

Row 1: Key K-014, Taken by Person A, Out 10/05, Returned 10/05, Status 2 Returned
Row 2: Key K-021, Taken by Person B, Out 10/05, Returned blank, Status 1 Out
Row 3: Key K-014, Taken by Person C, Out 10/06, Returned 10/06, Status 2 Returned
Row 4: Key K-007, Taken by Person A, Out 10/06, Returned blank, Status 1 Out
Row 5: Key K-021, Taken by Person B, Out 10/07, Returned 10/07, Status 2 Returned
Row 6: Key K-033, Taken by Person D, Out 10/07, Returned blank, Status 1 Out

A row with no Returned date and Status 1 Out is a key that is out now. Row 2 and Row 6 show the pattern: the same key can appear many times, and each trip is its own row.

Key control register, key log and sign-out sheet: same thing here

People call this a key control log, a key control register or a key sign out log. This guide builds all three the same way: a Keys list (the register of what keys exist) and a Key log (the sign-out record).

Why the clipboard sheet fails

A sign-out sheet is a good idea that breaks in ordinary ways:

People forget to write their name, or write it so no one can read it.
The sheet fills up, and the old pages go in a drawer.
To find who had a key last Tuesday, someone reads page after page.
Nobody sees the whole picture. You cannot ask "which keys are out right now?" without counting the hooks.
The sheet is in one place. The person who needs the answer is somewhere else.

Signs this is happening in your team

A key is missing and no one knows who last had it.
The sheet has gaps, or lines with no return time.
Two people think they have the same key.
The person who manages the keys is the only one who can find anything.

Plan the data before you build

Keep it small. You need two lists, because one key is taken many times. If you put everything in one list, you must either type the key details again each time or lose the history.

The Keys list answers: what keys exist? The Key log answers: who took which key, and when?

If you have not used SharePoint lists before, see "Replace a spreadsheet tracker with a SharePoint list". If you track tools or laptops instead of keys, see "Equipment checkout with no record: how to track who has what". This page uses the same two-list idea, tuned for keys.

Step 1. Build the Keys list

Create a new SharePoint list named Keys. One way to start with these columns:

Title. Rename it to Key tag and put the number on the tag in it, such as K-014. Give each key a tag number that is written on the key itself.
Door or area. Single line of text. Example: "Supply room". Write the place, not how to get in.
Status. A Choice column with these values: 1 In, 2 Out. Our advice, not a Microsoft rule: the number prefix keeps the values easy to tell apart. Set the default to 1 In.
Notes. Optional. Keep it to plain facts like "spare copy in office".

If you would rather not rename Title, you can turn off the Require setting for it in the column settings and hide it. Renaming is simpler.

Enter one row for each key. A key with spare copies gets one row for each copy, with its own tag.

Step 2. Build the Key log list

Create a second list named Key log. Columns:

Title. One way to start: rename it to Entry and let it hold a short label such as the key tag and date. If that feels like extra typing, turn off the Require setting for Title and hide it. Do not delete it.
Key. A Lookup column. Microsoft says that to add one you select Add column, then Lookup, name the column, choose the source list (Keys) and choose the source column to show (Key tag, which is the Title column). Microsoft lists limits: the lookup source column must be a type such as single line of text, number or date, and the lists must be on the same site.
Taken by. A Person column. People are picked from your organization.
Out date. A Date and Time column. Set the default to today's date.
Returned date. A Date and Time column. Leave it empty. It is filled in when the key comes back.
Status. A Choice column with these values: 1 Out, 2 Returned. Set the default to 1 Out.

The values in this list are different from the Keys list on purpose. In the Keys list, the key is In or Out. In the log, the row is Out or Returned. Keep the exact words the same wherever you use them later, including in the flow in Step 6.

Step 3. Make checkout fast

A sign-out that takes more than half a minute will be skipped. Two ideas:

Put a link to the Key log in a place everyone already opens, such as a pinned tab in a Teams channel or a bookmark on the shared computer.
Use the Add new item form, which only has the few columns above. Hide the ones people should not fill in. You can customize the list form; see "Make the new-item form shorter: sections and show-if rules" if you want to tidy it.

When a key is taken: add a row in the log (Key, Taken by, Out date) and change the key in the Keys list to 2 Out. When it comes back: open the log row, fill in the Returned date, set Status to 2 Returned, and change the key in the Keys list back to 1 In.

That is four edits for a round trip, and two of them are in the Keys list. This is the weak point of the whole design. If people skip the Keys list update, the Keys list is wrong. For that reason, one way to start is to treat the log as the truth, and use the Keys list status only as a quick glance.

Step 4. Make a "Keys still out" view

In the Key log, you want a view that shows only rows where the key has not come back. The simplest filter is on Status.

In the Key log, open the view menu and choose to create a new view, named Keys still out.
Set a filter: Status is equal to 1 Out. Microsoft's page on filtering a view says you can filter from a column heading: select the column heading, choose Filter by, pick the value and select Apply. Save the view by giving it a name.
Sort the view by Out date, oldest first, so the longest-held key is at the top.

If you want to filter on an empty Returned date instead of Status, or use [Me] to show only a person's own keys, or [Today] to show keys out for more than a set number of days, use the classic view editor (Edit current view, then Filters). Microsoft says [Today] and [Me] need it. Using it for a blank-date filter is our advice, not a Microsoft rule. Microsoft says [Today] and [Me] cannot be used when filtering from a column heading. In the classic editor you select the view name, choose Edit current view, then open Filters and choose Show items only when the following is true. Our advice, not a Microsoft rule: test any filter on an empty date with a few sample rows before you rely on it.

Do not try to make a calculated column that shows days out by using today's date. SharePoint blocks [Today] in calculated column formulas. Use a view filter or a flow for that.

Step 5. Make a few more views

All entries. Every row, newest Out date first. This is your history.
By key. Group the view by Key to see how often each key moves.
My keys. Filter Taken by to [Me]. Use the classic view editor for this one, as above.

Views are for convenience. They are not security. See the next section.

Step 6. Add a reminder (optional)

A reminder flow can email the person when a key has not come back. A scheduled cloud flow in Power Automate is the usual tool. This follows the same pattern as "Send a reminder before a due date in a SharePoint list", so this page gives only the outline.

Sign in to Power Automate. Select My flows, then the Cloud flows tab, then Create from blank.
Add a Recurrence trigger. Set Interval to 1 and Frequency to Day. Microsoft's page on running a flow on a schedule says to set the Time zone and Start time, and to pick the hour and minute when Frequency is Day.
Add the SharePoint action Get items. Pick the Key log list.
Open Advanced options. In Filter Query, use the column name, the operator and the value in single quotes, as in Microsoft's examples: Status eq '1 Out'. The value must match the Choice value in the list exactly, including the number.
Set Top Count. Microsoft says the default item limit is 100, so without this setting the flow gets only the first 100 rows. You can raise it up to 5,000. A key log can grow fast, so set it to 5000.
Turn on Pagination in the action settings. Microsoft says to enable Pagination on Get items when working with lists of more than 5,000 items with a filter query. Without it, the flow may return nothing if no match is in the first 5,000 items.
Add Apply to each and choose value from the dynamic content of Get items.
Inside the loop, add the Office 365 Outlook action Send an email (V2). Address it to the Taken by person and name the key and the Out date in the message.

The flow only sees the rows that Get items returned. If the log grows past 5,000 rows, archive old rows into another list first; see "Move closed items to an archive list so the main list stays fast" if you need that.

One flow email a day for each key that is out can annoy people. One way to start is to run it once a day and only for keys that have been out longer than you expect, which you set with a Condition after Get items.

What to avoid

Do not put door codes, alarm codes, safe combinations or where spare keys are hidden in the list. If someone gets into the list, they get that, too.
Do not use the list to decide who may take a key. Decide that with a person or a written rule.
Do not ask people to log keys "when they remember." Make it a rule that a key does not leave without a row.
Do not let the Status choices drift. If you change "1 Out" to "Out" in the list, update the flow filter too.
Do not keep a second paper sheet "just in case." Two records disagree.
Do not give everyone edit rights to every row if one person should own the log. See "Let each person see only their own items in a SharePoint list" and "See who changed a row: SharePoint version history".

Honest limits

A key log is a record. It is not a lock, and it does not make a building secure. It cannot tell you whether the key is really in someone's pocket, whether a copy was made, or whether the person named is the person who took it. It only holds what people type.

Views are not security. A filtered view like "My keys" hides rows from the screen, but it does not stop someone who has access to the list from opening the other rows. Permissions control who can see and change a list. If the log should be limited to a few people, set that with list permissions, and ask your admin.

The Keys list status can drift from the log if people forget the extra edit. Check the hooks against the list once in a while.

The flow in Step 6 only reminds. It does not recover a key.

Using Excel instead

A key log in Excel is one sheet with the same six columns as the sample above. It works when one person keeps the sheet and nobody else needs to see it. Our advice, not a Microsoft rule: switch to SharePoint lists when more than one person signs keys out, when you need a view of keys that are out now, or when you want a reminder email.

When to get help

You can build all of this yourself in Microsoft 365. If you would rather have it built and tested for your team, I do that. A tracker like this is a fixed price: $5,000 for one, built in 2 weeks. See "What a SharePoint tracker costs to build". Bring one example of the problem to a free call.

Tell me what's still manual

I am Al Davies. I run 250 High Street LLC and build SharePoint trackers and Power Automate flows for operations teams. I work remotely. The first call is free, and I will not try to sell you anything.

See trackers I built: Tools I built

FAQ

Can a SharePoint list control who can take a key?

No. A list records what people enter. It does not open or lock anything, so use a person or a written rule to decide who may take a key.

What does a key control log example look like?

One row for each time a key is taken, with key tag, taken by, out date, returned date and status, as in the sample above.

How do I make a key log in Excel, and when is SharePoint better?

Excel works with the same six columns on one sheet. Switch to SharePoint lists when several people sign keys out or you want a keys-out view and reminders.

What is the difference between a key control register and a key log?

The register lists the keys that exist (the Keys list), and the log records each checkout (the Key log).

How do I see which keys are out right now?

Make a view of the Key log that filters Status to 1 Out. Microsoft says filters with [Today] or [Me] need the classic view editor (Edit current view, then Filters).