Random Value from List Subtraction

[ezosoro]

It's working, but I am getting a lot of duplicates with this formula.

any(sort(([Team ID].[Van Preference]-Downers[Vin])-Todays Jobs[Van]))

Basically, in the Table ref by Team ID there's an enumlist with vins of vans that a driver can use. I'm subtracting any vins that are undriveable from a Downers slice, and (Trying) to subtract any jobs that are already assigned to that van's vin. The problem is its giving a lot of people a duplicate van. This is run by a bot, formula is in an initial value column.

It seems to be favoring the value in the middle of the list. How can I make it only choose unique values?

[lynchk21]

Couldn’t you make a slice that filters out both “downers” and “todays jobs”, call it say “available vans” and then use

INDEX(available vans[VIN], RANDBETWEEN(1, COUNT(available vans[VIN])))

?

[ezosoro]

Problem with that is it's pulling from the Team and Vans table to build a row in the Jobs table. Also there's certain vans that each person can drive. Like one might have 1,2,3 as a preference and someone else would have 2,5,10 etc. 

[lynchk21]

Hmmm.. It seems like you could still use a virtual column on the Vans table that indicates if it is available by checking the other relevant tables(jobs,downers), and then you could use a slice based off that virtual column in your random selection formula. As far as driver preference goes, you could build that into the formula it seems, or create a virtual column on the Vans table that indicates who can drive them.  Without being able to access everything it is hard to tell, but maybe this is a possibility? 

[ezosoro]

I think something like that would work. I made a virtual column in the Vans table that subtracts the vans used today from the available vans. With test, it appears to be working. The part i'm still struggling with is how to select one of the driver preferences if it's also in that available list.

[lynchk21]

To make sure I understand, when you are adding a new record to the Jobs table, you have a column that is randomly selecting a value that is available in the (available) Vans table, and you want it to only select a value that matches the drivers preference (which I am assuming is in some Driver table?) 

[ezosoro]

Almost.. when adding a Job record I want to randomly select a value from the driver's preference column of the Driver table, IF it appears in the Available Vans (slice of vans with driveable status), and if it has not already been used for a job record that day. (Which I have a slice "TodayVans" which subtracts Job[VIN] from Available Vans[VIN). The problem is I'm using a bot to create the Job records and it doesn't seem to look at the part of whether the van has already been used. For the Vans column in the Job table under initial value I have INDEX([Team ID].[Van Preference], RANDBETWEEN(1, COUNT([Team ID].[Van Preference]))). If i put anything in valid if it doesn't assign any Van values, so i've tried IF(IN(TodayVans... etc. but it will still assign duplicates or even vans that aren't available if they're in the preferences but not in the Available vans slice. I appreciate any help it's been driving me nuts for weeks.

[lynchk21]

What are the values in the driver's preference column? Numbers that correspond to the VIN - and what is the ID column in the Vans table? Sorry for the questions, just trying to picture your database. 

[ezosoro]

That’s okay! I know I didn’t explain well. The van preferences are the full vin in an enumlist. The ID column in the vans table is the VIN. And the Driver table with the van preference column has email as the id. In case that’s relevant. 

[lynchk21]

Maybe try:

ANY(SELECT(TodaysVans[VIN], IN([_this], [_thisRow].[Drivers_ID].[van preferences])))

or

ANY(SELECT(TodaysVans[VIN],IN([VIN], [Drivers_ID].[van preferences])))

[Sirfyaad]

@ezosoro 

any(sort(UNIQUE(([Team ID].[Van Preference] - Downers[Vin]) - Todays Jobs[Van])))

[ezosoro]

I thought this had worked but it does not. It's still giving duplicates. It works if I manually create a job where it auto selects a van that hasn't been used but when ran with a bot it doesn't work. Maybe because I have it in initial value? I don't really want it in the formula column as other actions won't fire. Nope putting it in the formula column doesn't work. This is so frustrating how it's favoring all the middle of the list values.