Field service- help to show sites strill open

AppSheet AppSheet Q&A
1

Hi everyone,

We use AppSheet for field service management of hired alarm and CCTV systems used on construction sites (engineers, architects, builders, etc.).

All the data comes from a single table. Each system has a unique serial number, stored in one column. Every time someone visits a site, a new row is created. Another column records the site activity/status using multiple choice values, such as installation, repair, adding equipment, moving the system, and finally several different options that indicate the end of the hire (there are four possible “closed” statuses).

What I need is a slice or view that shows only the sites that are still open, so I can cross-check them with my manager for invoicing purposes.

One complication is that the same equipment (same serial number) can be reused on different sites over time, so the serial number appears multiple times in the table.

I’ve tried to work this out using the available documentation and resources before posting, but I’m a bit stuck. Any guidance would be much appreciated.

Thanks in advance!

Mac Italy

2 Likes
2

And([status]<>”closed1”;

[status]<>”closed2”;

[status]<>”closed3”;

[status]<>”closed4”)

as the slice formula?

1 Like
3

Base on this comment above, are you able to assume that any entries for equipment used at previous sites will be in a “closed” status? In other words, is there only ever one site record “open” for each piece of equipment?

If so, then the expression @OptimiX_XcrY provided should suffice. By omitting all closed entries, you should only be left with all “open” status records and there should be only one per equipment.

You can simplify that expression like this:

NOT(IN([status], {“closed1“, “closed2“, “closed3“, “closed4“}))

If for some reason there can be multiple site entries in an “open” status for the same equipment, the expression becomes more complicated but is still doable. I’ll provide that expression only if you need it.

FYI…some additional pointers you may find helpful…..

If you plan to expand this app with additional functionality, you will find implementations difficult having all these details in a single table. You probably should think about a different data design. At the very least you would want:

Site table
Equipment table
Activity table

I hope this helps!!

3 Likes
4

Thank you I will have a look at this and give it a try to see what data it pulls from the table. I think the sheet I initially made, that populates was badly designed and probably not fit for the purpose of the app in question. We used only the sheet beforehand on Drive.

Following are the columns used in the table:

Date, Activity, Company, Contact name, Telephone, Site address, Technician name, Equipment type, Serial n., N. Sensors, N. RC, N. Signs, Tech Note

I checked and completely forgot that ACTIVITY, EQUIPMENT TYPE and COMPANY are from 3 other tables with the possibility to ADD new companies but no modification of existing. They appear as dropdowns in the app. This was added later due to people making mistakes in the input.

If I only filter all the closed instances then the same serial number used on a different site would appear from a previous installation?

Example: Serial IBR123 used on 5 different sites with rows of all the times onsite (installation, repair, site closed). How can I obtain only the most recent data without duplicating the same equipment serial already closed in another site?.

Sorry if it’s not very clear. If necessary can I make a dummy sheet with generic data and post it here if it’s allowed?

5

Hi and thanks for the answer, yes indeed the same equipment has been reused many times in different sites and has gone through Installation, Other sites visits then one of the Site closed statuses at the end. Each one creates a row in the table. So let’s say equipment ‘AA123’ has been used on 6 sites but is of course now only on the latest site. This equipment appears in 20 rows but only the last one is actually still active. All sites will have had Installation, other Onsite Activities and one of the Site Closed statuses. Each one creates a new row every time a technician goes onsite. They use the copy feature from the first installation to avoid getting the address or Customer Contact info wrong and then modify date and anything else necessary before saving.

As said to @OptimiX_XcrY I only realised last night that there are actually other tables for Activity, Company and Equipment Type which populate a drop-down that I had completely forgotten about (apologies for the lack of clarity). They are ‘add only’ and were an afterthought a couple of years ago.

I agree that most probably more though should have gone into the design of the data. I now have almost 4000 rows.

Thanks in advance.

1 Like
6

Others who have already responded to you may not be at their desk now. Hence requesting you some more inputs which could help them or the community to help you better. Could you mention
A) what is the key column of this table?

B) which column in the table captures the equipment status (installation, repair, site closed) . Is it “Activity” column?

C) You earlier mentioned there are 4 possible closed statuses , could you mention what are those. Will any one of those 4 closed statuses considered as closure of that particular hire cycle.

D) Is there any unique " hiring" number assigned when an equipment is given on hire to a company. For example Serial IBR123 given on hire to company “Great Constructions” on 12/12/2025 will have a unique hire number for that entire hire cycle -installation, repair, site closed and so on. In short this unique hire number remains active during the entire hire cycle for that company. A new hire number is allocated for a new hire cycle of that equipment.

You are right about more suitable design could make things easier to retrieve historical data and manage various operations.

@WillowMobileSys has already suggested some good options in structuring data.

1 Like
7

Hi Suvrutt Gurjar and thank-you.

I will try to answer as best I can

A) The key column is “Id” with initial value= UNIQUEID(), this column is hidden on the app.

B) The user has to choose one of the preset statuses via a drop-down (Enum) in the “Activity” column when compiling the form. No other values are allowed.

C) Yes the statuses that indicate the end of an invoicing cycle are:

“Collection” (when other systems will remain onsite at the same address afterwards)

“Removal“ (when other systems will remain onsite at the same address afterwards)

“Removal-site closed”

“Collection-site closed”

All of these instances close a hire cycle for invoicing purposes on that specific equipment.

C) No there is no unique hiring number. It was not something that came to mind when loading the data.

I hope this helps.

Mac

2 Likes
8

Thank you @Mac2020 .

I am sure @WillowMobileSys or @OptimiX_XcrY will respond to you with a solution with these more details from you.

I can also suggest, however it may not be appropriate for me to start suggesting when they are already engaged in this post. I will join, if I feel my suggestion can be useful as an alternative approach.

2 Likes
9

I think what is missing is an understanding of what the “closed” status’ actually mean.

From what I gather, a piece of equipment could have several entries from the SAME SITE - an installation entry, a repair entry, a moved entry, etc.

My understanding is that LATER this site becomes “closed” for some reason or another. So when you filter you want ALL those rows associated with that “closed” site to be removed.

  • Under what circumstances does a site get marked “closed”?
  • **Additionally, can a site NOT be marked “closed’“ but NOT have any equipment installed and still needs to be filtered out - or is this one of the “closed” status’?
  • Can a site be “closed” in the past and be “opened” again such that you want all past rows filtered but current rows kept?**

The best way to deal with this situation is to have a Sites table with a Status column. The site then is a REf column in your Activity table. The Status column is either “Open” or “Closed” and maybe even “Active” to indicate you have equipment on site. Then in your Activity filter Slice, through the Ref column you can do something like NOT([Site].[Status] = “Closed” and eliminate all rows associated with that particular site.

*********************************
I promised an expression if you needed it. Below is that expression based what I understand you need described above. Its not as bad as I thought based on my newer understanding but there are still some questions who’s answers can dramatically change this.

Basically, a row is to be included if there are NO other rows for the same site marked as “closed”. Like this:

COUNT(SELECT(Activities[ID],
AND([Site] = [_THISROW].[Site],
NOT(IN([status], {“closed1“, “closed2“, “closed3“, “closed4“}))
))
) = 0

I hope this finally helps!

4 Likes
10

Hi,

Many thanks for the answer, I will try this expression and get back to you as soon as possible.

:+1:

2 Likes
11

For this last part, you could use a “closed date” to filter out items for the site prior to the closed date. And since it now seems you might have more than one closed date, you would need to get the MAX closed date.

The expression would need modified to something like so:

OR(
COUNT(SELECT(Activities[ID],
AND([Site] = [_THISROW].[Site],
NOT(IN([status], {“closed1“, “closed2“, “closed3“, “closed4“}))
))
) = 0,
[Activity Entry Date] > MAX(SELECT(Activities[Closed Date],
AND([Site] = [_THISROW].[Site],
IN([status], {“closed1“, “closed2“, “closed3“, “closed4“})
)))
)

Basically, if there are NO closed entries then return the site row - assumes it is a current site activity. If there are any “closed” entries for the site, then return ONLY those with newer dates than the LAST “closed” entry for the site - assumes they are current site activity.

Because, this expression results in TWO potential table scans for EVERY row being considered as included in the Slice, this is EXTREMELY inefficient.

*******************
I had mentioned previously including a Site table because you had characterized the site itself as “closed”. But since you can return to a site later, I believe you should think of these as Projects. The site itself doesn’t “close” - the Project does.

This allows you to group all your activity under a specific Project ID. Site is just a piece of information for the Project. Then you can filter Projects by “open” or “closed” and all of the activity records, as child rows, just come along for the ride.

I hope this finally gets you to where you want to be!!

4 Likes
12

Yes indeed, I will try this expression first (using a copy of the app with copied data) and see where it takes me. If I see a notable lag then I will try to redesign the tables as indicated. I’m going to have to put my learning hat on to get there. That’s part of the fun to be honest.

Unfortunately, the reason why I jumped into Appsheet was when I came into the company, which had literally exploded with large volumes of work, they managed things via WhatsApp or a notepad and many invoicing mistakes were made :scream:. The original sheet I created was continuously compiled with errors/, omissions by the other technicians late at night or forgotten about completely which meant the same problems reappeared . I then decided to create the app, giving serial numbers to all the equipment etc, so as to have a reasonable working structure as previously there was only “organised chaos”. This certainly made life much easier than before but as you had mentioned, had it’s limits due to the structure of the data in the app and take it to the next level.

I will mark this a closed. MANY THANKS to you and the other experts for all the help given!.

Best wishes, much appreciated. Thanks :+1:

Mac

2 Likes
13

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.