Hi All. Just wanted to see if anyone has any...

[David_Jones1]

Hi All.

Just wanted to see if anyone has any opinions on a better way of achieving something.

The scenario is that I have an app used by volunteer Blood Bikers to record collections and deliveries of blood and samples etc. between hospitals.

So in essence it’s basically a standard logistics type app.

The current process is that a coordinator takes a call from a hospital and records the details with an initial Status of “Awaiting Allocation”.

They then call a Rider to allocate the job and they move the Status on to “Called Rider”.

When the Rider arrives at the hospital they edit the job and manually move the Status to Collected and obtain a collection name and signature and record the time of collection.

They do the same when they deliver the job.

This is time consuming as the whole record is displayed and they have to scroll down alot to find the name and signature fields and invariably they forget to change the status or even select the wrong status.

I’ve created a new version of the app which attempts to make the Collection and Delivery phase as simple and automated as possible for the Riders.

Up until the arrival of multiple actions this was difficult to achieve but now that you can run multiple actions, this is what I’ve come up with.

I’ll describe the Collection recording - Delivery is identical:

If Status = “Called Rider” an Action button is displayed called “Mark as Collected” This action carries out the following: 1 - Updates the Status to Collected - This in turn automatically updates the collection time as that is set as a Change Timestamp field. 2 - Loads a form with Quick edit fields for Collection Name and Signature.

Because they are quick edit fields as soon as they complete them the data is synced back a field at a time.

This is sort of OK and works, but one annoying thing is that when I load the form to collect the name and signature, this means that the status is now Collected because the first action has done that.

Because I have a similar action set up to deal with the delivery and that is set to display when Status = Collected, that now displays on the form whilst they’re still dealing with the Collection Name and Signature.

I’ve tried changing the order of the grouped actions so that I load the form first and get the name and signature but that means that the Mark As Collected action button is still showing and there is nothing to force the program flow to go to the Status Update action to mark the job as Collected when they’ve got the name and signature.

Screen shots atatched to illustrate what I mean:

I think there may be a better way of achieving this.

Anyone got any ideas please?

Thank you David

[David_Jones1]

@Aleksi_Alkio

New job created by coordinator and status set to “Awaiting Allocation” or “Called Rider” as appropriate.

But status before the goods are actually picked up should be “Called Rider”.

At point where Rider collects the goods from the Hospital, I want to do the following to minimise the amount of input the Rider has to do: 1 - Set status of Job to “Collected” automatically 2 - Record collection time automatically 3 - Force the Rider to obtain collection name and signature and then save the record.

The same scenario happens when they get to the drop off location and the job needs to be recorded as “Delivered”

Been playing with options for nearly a year now to try and achieve this through combinations of Actions, Workflows, Required_If, Forms, Detail views etc. but nothing will work exactly how I want it to above.

BTW, Status has to be a real column in the data as I have lots of Google sheet pivot tables that use Status to filter what gets reported.

[Aleksi_Alkio]

You could create two slices… one for collection and one for delivery. Then you could call those two slices with an action button with the deep link LINKTOVIEW. Instead of status field you could use virtual status and then you would not need to change the status manually. When the collction name & signature is filled, it will change the status automatically. With the slices you can control what fields it will show in your form. Though you could do the same with detail view as well.

[David_Jones1]

@Aleksi_Alkio Thanks for your suggestion.

I think I follow some of this.

I guess the slices will just have the Job number, Status, Collection Name, and Signature in the Collection slice and similar for the delivery slice.

And then I create a ref view to those slices and use the LINKTOVIEW to call them.

But could explain what you mean by the Virtual Status?

And what about the collection and delivery timestamp fields?

Would they need to be included in the slices or will they get updated anyway when the slice view changes the status?

[David_Jones1]

+Steve Coile Hi Steve.

OK, thanks for the suggestion about a workflow rule.

That’s not something I hadn’t considered. I’ll see if I can work out a suitable way of using a change timestamp to trigger it.

I’ve never had much success with quick edit columns because of RequiredIf columns that cause an error as soon as a value in a quick edit column gets saved back to the database if they’re linked.

So I’m interested in what you’re saying about a slice with just the name and signature columns in it and set as required.

I might be missing something in my Appsheet knowledge but I thought that a slice doesn’t have any customisation about whether a column in it is required or not.

I thought that a slice simply takes on the properties of the selected columns in the underlying table.

I can’t make collection name and signature required in the main table because that would prevent a new record being created without them being filled in.

Similarly, with forms they are a bit limiting.

If I had a slice with just those 2 columns and created a form for it then I can’t show additional information on the form to reassure the rider that they’re getting the signature for the correct job.

So at the moment I have a detail view where I show the job number and collection/delivery locations and status as header columns.

It would be really great if forms offered the same customisation of columns that detail views do.

Anyway, I’ll have a play with the workflow suggestion.

I guess I could make the collection timestamp link to collection name being updated which could then trigger the workflow to change the status.

[Steven_Coile]

Hmm… If the completion of the bulk of the form does not include the name and signature, you could use a slice to remove the name and signature from the main form. By removing them from the main form, their REQUIRED flags and Required_If formulas will be ignored for the main form, but will be enforced when the two columns are included in the slice to collect them. I’d expect.

There’s no reason the slice & form to collect name and signature couldn’t also include additional fields for informational purposes.

[David_Jones1]

+Steve Coile From my experience with forms, there is no control over what is shown for info only.

If a field is in the data that the form is linked to, whether that’s a slice or a table, then all fields defined as editable within the form are available.

But I don’t want to open anything up to finger trouble by the riders!

I think my only option is to use a Detail view and this means the rider will just have the additional step that they need to hit the edit button.

[David_Jones1]

@David_Jones Unfortunately I’m ging round in circles now and can’t find any solid way of achieving what I need to do.

It doesn’t seem to matter what flow of logic I try, I can’t set the collection name and signature to required but only if the user is on the collection form.

Until the job status becomes Collected I can’t enforce a required_if conditon on collection name.

I can’t have Collection Name as Required in the main data because it would be required from the point that the job is created.

As far as I can see it does boil down needing a feature that is not currently available in Appsheet i.e. on a form or detail view (regardless of the source of the data) be able to specify not only which columns to display on the form and what order but also to make them required.

Alternatively, be able to use the name of the view in logic conditions i.e. In main data the required_if condition for collection name would be: AND([Status] = “Collected”,CurrentView = “Capture Collection Name”)

I think I’ll put this through as a feature request to Appsheet.

[Aleksi_Alkio]

@David_Jones Back with this post Would you please summarize (short one) your need and I will try to find a way.

[Aleksi_Alkio]

#1 - Create two slices & Form views, Collection and Delivery. #2 - Choose ID, CollectionTimestamp, CollectionName and CollectionSignature for the Collection slice #3 - Choose ID, DeliveryTimestamp, DeliveryName and DeliverySignature for the Delivery slice #4 - Both Timestamp fields are ChangeTimestamp fields where you check either CollectionSignature or DeliverySignature fields. #5 - Create an action, “ActionStatus” and trigger it with the “Event Action”. #6 - For the ActionStatus write the condition rule ISNOTBLANK([CollectionSignature]). This will take care that this event action won’t be triggered if there are no signatures… for example when the admin is generating the record. #7 - For the status value you would need to create a suitable formula what the action should write. It could be something like…

IF(ISBLANK([DeliverySignature]),“Collected”,“Delivered”) #8 - Create two different LINKTOFORM action buttons for your detail view where you can open the correct form view (correct slice).

I didn’t test this but it should work.

[David_Jones1]

@Aleksi_Alkio Thanks Aleksi.

I’ll give it a go.

Just for clarification, when you say that ID needs to be in the slice, do you mean the record ID which I use UNIQUEID() to generate on a new record.

That’s different to our job number which is a readable sequence number.

[Aleksi_Alkio]

Yes, I mean record ID. Slice always need to have it… though you can hide it from the form.

[Steven_Coile]

“Virtual status” would be a virtual column that computes the status automatically, versus the “physical” status column you have now that must be set manually or through an action.

[Steven_Coile]

Include the timestamp column(s) you want updated in the corresponding slice(s).

[David_Jones1]

+Steve Coile OK, understood about the timestamp columns.

With the virtual column for status, if I understand VCs correctly, that means that our back end data would never actually get the status recorded against a job wouldn’t it?

That would be a problem for all our Google Sheet reporting dashboards where status is used to determine if a job should be included in a report i.e. only if Status = Delivered does it get reported.

We also report on Jobs Cancelled and Jobs Refused which are other Status values.

Doesn’t it also mean that the status has to be calculated dynamically all the time?

How would that work in the UX?

We have an Active Jobs view which displays all jobs with a status of Awaiting Allocation, Called Rider or Collected, a Completed Jobs view for all jobs for the past 3 days with Status of Delivered.

We also group jobs by status on the Active Jobs view.

[Aleksi_Alkio]

Do you need the status for other purposes than this app?

[David_Jones1]

@Aleksi_Alkio Yes, we have reports in Google sheets e.g. pivot tables that filter based on status.

[David_Jones1]

@Aleksi_Alkio +Steve Coile Still struggling with this and have come across what appears to be a fundamental flaw with the virtual column for Status proposal.

The slices for Collection and Delivery are based on a filter condition using Status!!

So if I have Status as a Virtual Column then surely these slices can’t work as they need real data from the database.

Or is there more magic around Virtual Columns that aren’t apparent to me?

[Steven_Coile]

I think your original approach of modifying the status column manually or with an action is the right one for your situation, versus the virtual column.

[Steven_Coile]

Workflow! You want a workflow rule that updates the status once both name and signature have been collected. If you have change timestamps for both name and signature, you can trigger the workflow on row updates, and check whether both column timestamps have been updated.

You might want to reconsider the use of quick edit columns for name and signature, as they allow one but not both to be collected. Consider instead a form (slice) with only those two (visible) columns and make both columns required. Then you wouldn’t need to use workflow and could instead update the status as part of the form or using an attached action.