Value in a Drop Down once selected should not be shown (based on a condition)

[Kanha_PM_Office]

Hi,
I have a table where one user inputs the Type of Vehicles needed.

Another user allocates the available Vehicles against the corresponding Vehicle Types.
But, once selected, the particular value should not be allowed to be selected or not be available in the drop-down list.

How can I achieve this in the Valid_If condition?

Thank you.

[Henry_Scott]

@Kanha_PM_Office, maybe if you post this under Questions and not Tips & Tricks. That way it is more relevant and will get the appropriate attention from the Community to respond

[Kanha_PM_Office]

Thank you Henry.
I have changed it.

[Henry_Scott]

Great. Hope you get a quick answer

[Lynn]

Hi @Kanha_PM_Office Do the vehicles ever become available again?

[Kanha_PM_Office]

Yes. The next day.
To make it more precise & clearer,
I have a table where the Supervisors would be requesting for a particular Vehicle Type for that day.
Accordingly, the fields of the same form, Allocated Vehicle & Driver would be selected from the drop down list of Vehicles corresponding to the Vehicle Type by the Vehicle Coordinator.
Now, I want the Allocated Vehicle field to be validated where once the vehicle is allocated for a shift in that day, it should not be available again until the next day.
Hope this helps.
Thank you team!

[Suvrutt_Gurjar]

Hi @Kanha_PM_Office,

You may wish to add details related to how you have constructed the two enums- Vehicle Type and Vehicle.

Presuming you are using another lookup table to implement dependent selection of vehicle type and vehicle , you may wish to try an expression something like below in your Vehicle column’s valid if.

SELECT(Lookup Table Containing Lookup Vehicle Type and Vehicle List[Vehicle] , [Vehicle Type]=[_THISROW].[Vehicle Type]) - SELECT(Table Name That Contains Vehicle and Vehicle Type Selection & Allocation Form[ Vehicle],AND([Vehicle Type]=[_THISROW].[Vehicle Type], [Vehicle Status]=“Allocated”,[Shift Date]<>TODAY())) +LIST([Vehicle])

Here [Vehicle Status] is an Enum column with two statuses ,"Allocated"and “Not Allocated” and to be assigned by the supervisor while allocating the vehicle.

[Shift Date] is the date when the vehicle is allocated and may be auto populated with an initial value of TODAY().

Exact implementation may vary based on your app design of other columns and table structure etc. Hope this helps.

[Suvrutt_Gurjar]

Hi @Kanha_PM_Office,

I realize that there needs to be one syntactically small but major change in expression related to [Shift Date] comparison with TODAY()

I request you to try following expression, if you are trying to use the valid_if in the Vehicle approach.

SELECT(Lookup Table Containing Lookup Vehicle Type and Vehicle List[Vehicle] , [Vehicle Type]=[_THISROW].[Vehicle Type]) - SELECT(Table Name That Contains Vehicle and Vehicle Type Selection & Allocation Form[ Vehicle],AND([Vehicle Type]=[_THISROW].[Vehicle Type], [Vehicle Status]=“Allocated”,[Shift Date]= TODAY())) +LIST([Vehicle])

[Kanha_PM_Office]

Sure @Suvrutt_Gurjar, I’ll definitely try.
Thank you for your time & effort.

[Lynn]

Hi @Kanha_PM_Office I have a demo App that uses a workflow to update a drivers table when they are assigned a job. This marks the driver as assigned and the driver is no longer available for jobs. When the job is “completed” a similar action then makes the driver available again. If you wish to check it out it is the "Trucks WF " app in my portfolio.
https://www.appsheet.com/portfolio/129805

[Steve]

Assuming a column named Allocated? (of type Yes/No) in the Vehicles table that indicates the vehicle described by the row is currently allocated and therefore unavailable, the following Valid If expression will display only available vehicles:

FILTER(
  "Vehicles",
  NOT([Allocated?])
)

[Kanha_PM_Office]

SELECT(All Heavy Vehicles[Reg #] , [Vehicle Type]=[_THISROW].[Vehicle Type]) - SELECT(Vehicle Allocation[ Allocated Vehicle #],AND([Vehicle Type]=[_THISROW].[Vehicle Type], [Status]=“Allocated”,or([Date]= TODAY(),[Date]=TODAY()-1),[Shift]=[_thisrow].[Shift])) +LIST([Allocated Vehicle #])

I am using this in Valid_if which is NOT working for me.
Here,

1. All Heavy Vehicles - Table that contains the list of all vehicles (with Vehicle Type, Registration #
2. Vehicle Allocation - Table with Slice (Allocated Vehicle #, Driver) for the particular Vehicle Request.

[Suvrutt_Gurjar]

Hi @Kanha_PM_Office,

Could you please mention exactly what is not working- is there a syntactical error or something else?

Also since last post thread, there are two more parameters introduced in the expression, previous day’s inclusion([Date]=TODAY()-1) and [Shift] column instead of [Shift date]. Could you please mention how this [Shift] column is defined for various days and how many shifts are possible in a day?

[Kanha_PM_Office]

I think…I have got the formula.Thank you.

SELECT(All Heavy Vehicles[Reg #] , [Vehicle Type]=[_THISROW].[Vehicle Type]) - SELECT(Vehicle Allocation[ Allocated Vehicle #], AND([Vehicle Type]=[_THISROW].[Vehicle Type], [Status]=“Allocated”,or([Date]= TODAY(),[Date]=TODAY()-1), [Shift]=[_thisrow].[Shift])) +LIST([_thisrow].[Allocated Vehicle #])

[Suvrutt_Gurjar]

Hi @Kanha_PM_Office,
Thank you for update. Nice to know the formula works as per your need.
As per my understanding of your given updates , following expression also should work
SELECT(All Heavy Vehicles[Reg #] , [Vehicle Type]=[_THISROW].[Vehicle Type]) - SELECT(Vehicle Allocation[ Allocated Vehicle #],AND([Vehicle Type]=[_THISROW].[Vehicle Type], [Status]=“Allocated”,or([Date]= TODAY(),[Date]=TODAY()-1),[Shift]=[_thisrow].[Shift])) +LIST([Reg #])

[Kanha_PM_Office]

Hi @Suvrutt_Gurjar,
I don’t understand the use of LIST command in this formula.

This SELECT(All Heavy Vehicles[Reg #] , [Vehicle Type]=[_THISROW].[Vehicle Type]) - SELECT(Vehicle Allocation[ Allocated Vehicle #], AND([Vehicle Type]=[_THISROW].[Vehicle Type], [Status]=“Allocated”,or([Date]= TODAY(),[Date]=TODAY()-1), [Shift]=[_thisrow].[Shift]))+SELECT(All Heavy Vehicles[Reg #],[Vehicle Type]=“NONE”) formula is also working for today & yesterday but, when I try allocate for any other date,for that particular shift, the vehicles which I have allocated are not shown!!

The Vehicle List is not showing the already selected vehicle for Today or Yesterday.

The vehicles which I have allocated for the same day but, for other shifts are being shown, which is actually good.

Also, I would be glad,if I have the same achieved for the Drivers. But, I have Drivers from
Table - Driver Master Data, which has Driver Id, Driver Name (Key & Label).

I couldn’t achieve it for the Drivers.

Thank you.

[Suvrutt_Gurjar]

Hi @Kanha_PM_Office,

The vehicles are not getting selected for the same shift on other than today or yesterday because I believe the current logic assumes that the vehicles will be available again only after de-allocation for other dates. One may of course take care of this new condition that the vehicles need to be available for other days (than today and yesterday) even if these are allocated. I believe the expression will be something like

SELECT(All Heavy Vehicles[Reg #] , [Vehicle Type]=[_THISROW].[Vehicle Type]) - SELECT(Vehicle Allocation[ Allocated Vehicle #], AND([Vehicle Type]=[_THISROW].[Vehicle Type], [Status]=“Allocated”,or([Date]= TODAY(),[Date]=TODAY()-1), NOT([Date]>TODAY()),NOT([Date]<(TODAY()-1)), [Shift]=[_thisrow].[Shift]))+SELECT(All Heavy Vehicles[Reg #],[Vehicle Type]=“NONE”)

The highlighted part will make the subtract select statement ineffective on other days than today and yesterday. I also could not test it. I will add tomorrow if I have any concrete suggestion.

I also request you to revisit all conditions in general, because since the expression conditions have become complex, there is a likelihood that some other unforeseen scenario may come up. For example what if the allocated vehicles are no again de-allocated once the two day period is over.

Hope this helps.

[Suvrutt_Gurjar]

Hi @Kanha_PM_Office,

Please ignore the above expression. Could you please try with an expression , something like

SELECT(All Heavy Vehicles[Reg #] , [Vehicle Type]=[_THISROW].[Vehicle Type]) - SELECT(Vehicle Allocation[ Allocated Vehicle #], AND([Vehicle Type]=[_THISROW].[Vehicle Type], [_THISROW].[Status]=“Allocated”,OR([_THISROW].[Date]= TODAY(),[_THISROW].[Date]=TODAY()-1), [Shift]=[_thisrow].[Shift]))

I am unsure if the following part of the expression is necessary after above changes- SELECT(All Heavy Vehicles[Reg #],[Vehicle Type]=“NONE”)

Please include this part if necessary after testing the above suggested expression…

Hope this helps.

[Kanha_PM_Office]

Hi @Suvrutt_Gurjar,
Thank you for your time & effort.
I wanted to include “None” to enable the user to select an option of not allocated.

Apart from that, I couldn’t find even Driver Name showing in the form.
Valid_if…
SELECT(Driver Master Data[Driver Name],[Driver Name]=[_THISROW].[Driver Name]) - SELECT(Vehicle Allocation[Driver Name],AND([Driver Name]=[_thisrow].[Driver Name],[Status]=“Allocated”,or([Date]= TODAY(),[Date]=TODAY()-1), [Shift]=[_thisrow].[Shift])) is what I use.

I have no idea why it’s not showing.

[Suvrutt_Gurjar]

Hi @Kanha_PM_Office,

I believe you need to use for this driver’s expression ,something like below

SELECT(Driver Master Data[Driver Name], TRUE) - SELECT(Vehicle Allocation[Driver Name],AND([Driver Name]=[_THISROW].[Driver Name],[_THISROW].[Status]=“Allocated”,or([_THISROW].[Date]= TODAY(),[_THISROW].[Date]=TODAY()-1), [Shift]=[_thisrow].[Shift]))

[Suvrutt_Gurjar]

So essentially, in both the vehicle and driver expressions, the general format is

{All the list or minimal conditional list of Vehicles or Drivers} - {Conditional list of Vehicles or Drivers}
In vehicles expressions, in the first list, the list is narrowed by just the vehicle types. In case of drivers , if there is no such type ( for example heavy duty vehicles drivers or passenger vehicle drivers) then I believe the first list can be unconditional for drivers.

[Kanha_PM_Office · Accepted solution]

Yes.

There can’t be without type. The requester should mention Vehicle Type.
It’s a mandatory field, based on which the Vehicles are auto populated as Drop Down List.

Also, I have these formula working for that particular day or the previous day. (However, I couldn’t achieve the same for any other days.But, that’s not a criteria to worry about).

I am so glad that I could get immense help from you & your team! Kudos!!

1) For Vehicle Allocation:
SELECT(All Heavy Vehicles[Reg #] , [Vehicle Type]=[_THISROW].[Vehicle Type]) - SELECT(Vehicle Allocation[ Allocated Vehicle #], AND([Vehicle Type]=[_THISROW].[Vehicle Type], [Status]=“Allocated”,or([Date]= TODAY(),[Date]=TODAY()-1), NOT([Date]>TODAY()),NOT([Date]<(TODAY()-1)), [Shift]=[_thisrow].[Shift]))+SELECT(All Heavy Vehicles[Reg #],[Vehicle Type]=“NONE”)

2) For Drivers:
SELECT(Driver Master Data[Driver Name],true) - SELECT(Vehicle Allocation[Driver Name],AND([Status]=“Allocated”,or([Date]= TODAY(),[Date]=TODAY()-1), [Shift]=[_thisrow].[Shift]))

These are working well for my usage criteria.

Apart from this,
Is there any possibility for the user to enter the values directly in the Table form view instead of opening in the form & using a drop-down list. Precisely, is there any option to use like a drop-down list for the Vehicle & Driver fields directly from the table view? If so, how to achieve this. It would actually save lot of time & effort for our users.

Thank you again!

[Suvrutt_Gurjar]

Hi @Kanha_PM_Office,

You are welcome. Glad to know that the expressions help you. Thank you for your appreciation. Wish you the best with your app.

Just to clarify on one point, if any confusion, I would like to mention that, I am unsure what you mean by " your team". I am also an AppSheet enthusiast and an AppSheet solution partner. Others in this thread are also helpful senior community members and AppSheet experts in their own right. So they are not “my” team but our senior community colleagues.

As for edits in directly in the table, there is a feature called “Quick Edits” that one can enable in the table view settings pane in UX - > Views . However, it is in beta stage as per my understanding and I have not used it. I will request you to start a new thread for this query so that other community colleagues and AppSheet team experts can offer their insights.

[Kanha_PM_Office]

Oh. Okay. Anyways, thanks.