Delete list item (in another list) based on calculation in current list, and query of dates

  • 17 July 2020
  • 8 replies
  • 26 views
D
Badge +3

Here's what I'm trying to do - 

I have a (lookup) list of dates, called AvailableClassDates.

I also have a list that is intended for people to sign up for classes on those dates. This uses a lookup column to see the other list.

 

My workflow concept is this:

If the number increment =10, the workflow will delete the date in the lookup list - Thereby making the date unavailable to choose from that point on.  

 

I've tried and tried to come up with a query with a certain date (in other words, a COUNT), but I can't seem to get anything to work.  

 

I've tried a query, but can't make it work.  Attached are screenshots of what I have so far - with the edit view at the end.  What am I doing wrong?

 

8409iF88878B4C708626F.jpg8410iDA170E5A4E199151.jpg

8411i5D1616BCF91553EB.jpg8412i7792096227A2AB72.jpg

8413i8ABEABAA822CC1BC.jpg

 

 

8 replies

T
Badge +8

Hi @danjact,

@danjact wrote:

I also have a list that is intended for people to sign up for classes on those dates. This uses a lookup column to see the other list.

 Why not utilize the lookup column instead of filtering by a date?

  • Edit your column to display the id of the selected element (I´ll call it MyDate:ID)
  • In your query, get all items from ClassDate, where ID = MyDate:ID
  • Collection operation -> count
  • If count >= 10 (you never know when two items get submitted at a bad timing 🙂 )
    • Delete from ClassDate, where ID = MyDate:ID

 

D
Badge +3

This is a sound approach, but let me show and tell you what I did - 

If I'm understanding you correctly, the image shows what you instructed:

8415iD247087138A729D6.jpg

This doesn't seem to work.  Please let me know if I have something wrong here.

 

However, when I set the filter to read, if ClassDate:ID = 2 (for example, as the ID of the item in the other list of dates), it works.  

 

What am I missing here? @Tarf ?

T
Badge +8

Hi @danjact,

sorry my latest instructions were wrong.

I wrote:

In your query, get all items from ClassDate, where ID = MyDate:ID

(Which would result in ever only 1 item being returned, oops!)

But meant:

In your query, get all items from the Attendees list, where MyDate:ID = CurrentItem:MyDate:ID (e.g. items which are assigned the same Date)

That way you get all the attendees on the same Date and then count them.

Sorry!

D
Badge +3
No worries - this works. Thanks!
D
Badge +3

I do have one other question about this one - Let's play this scenario:

Let's say 10 list items (people signing up for one particular date) make that date get deleted from the date list.  Then someone removes their name from the signup list (in other words, deletes the list item).  At that point, the old date is manually entered back into the list, because there's one spot available.  

 

When a 10th person comes along and chooses that date, what can I do in the workflow to make it remove that date again?  As it stands now, it won't do that.  

 

Let me know if I need to clarify my question - and again, thank you. @Tarf 

T
Badge +8

Hi @danjact,

in this case I´d recommend not deleting the date from MyDate but updating it instead to be "inactive".

  • Add a yes-no column to your dates list -> name: inactive; default: no
  • Add a view to your dates list -> name: Active: filter: Where inactive === no
  • Modify your form to display only dates from the view "Active"
  • In your workflow, instead of deleting the element, update it´s inactive field to "yes"
  • You would then manually (or per workflow) re-activate the Date by setting the inactive field to no when someone cancels their signup
D
Badge +3

@Tarf  - How would I modify my form?  (I'm not using Nintex Forms, only Workflow.)  The list of dates in the form is coming from a lookup column that isn't giving me the option to base the lookup on a view.  

T
Badge +8

Hi @danjact,

to filter the value without Nintex Forms you can use this trick using a calculated value column:

(from: https://sharepoint.stackexchange.com/questions/18247/how-to-make-a-filtered-lookup-field)

In addition to your "Deactivated" field, create a Calculated field named "ActiveTitle" (or whatever you want to call it). Use the following formula:

=IF(Deactivated,"",Title)

The ActiveTitle field will be empty when the Deactivated field is set. When you configure your lookup column, tell it to use the value from ActiveTitle instead of Title. Empty values will not show up in your lookup list, so you will end up with only the values that are not Deactivated


Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings