Can I get help on editing a list so that we do not allow duplicates to be entered

It is possible to loop through all items in a list (for each loop condition) and do a data comparison to check for duplicates. if a duplicate is found, show a message and clear the user-entered value out. You can also do this loop on "Save" and prevent the save from executing if you find duplicates. Hopefully this points you in the right direction.

I am headed in the looping direction, and doing an advanced condition for a compare.

I get the logic of what I need to do, I just do not understand what items in the context browser (Display rows, add/edit rows) values, I need to compare.  I have been trying different things for a while, so I was asking for an example to follow.

I do have a good feeling that it is possible to do this, but I still need a concrete example of how.  Anyone seen an example of this in a smartstarter, or have a routine?

BillK

I'm going to try and describe my quest, since I have not achieved the result I need.

I have a smartbox object which is used to populate a drop down list control.  The list will contain different students in a specific class.  For example, the 1st class could return a list of 3 students, Wendy, Bill, and Lydia.  When the form is initialized, I have all 3 students to choose from.  I will add the first student selected to a separate list view.

What I want to happen is that when a student is selected, they are removed from the populated dropdown for future selection.  So, if Lydia is selected, Lydia is added to students selected list, and the new dropdown only lists Wendy and Bill as possible choices.

How can I do this with a filter?  and only add students to the selected list once?

I tried to log a help ticket on this, and eventually came back with go talk to remote mentoring.   Is there any example of form rules which can do this?

Hmm... Would it be ok to persisist this data as it is changed? That would make this easier... by that I mean when you select a student, you flag them in your data source as being "Selected" and re-populate your dropdown to exclude "Selected" students. I'm not sure what your data source for students is, but if you can add a selected flag to it that would be easiest.

If the above is not possible, I have some other thoughts... When you select a Student, append their name (or, ideally, an ID) to a hidden data field and repopulate your student list. The filter on the populate student list can have a "where id not in [hidden data field]. That is a bit vague I know, I'm hoping the first option will be possible as it is cleaner.

Hi Tbyrne777!

Thanks so much for responding.

I understand where you are going with the repopulate in your first solution, but that is not possible in our case.  We are using stored procedures to select the data, and the data lists are being returned from Peoplesoft.  We are a school.  We provide a term (semester), the instructor, and the course, and a list of students is returned.  I cannot update these lists.

I have been trying to work on the data field option to build a list of ID's that are selected.  Concatenate as an expression doesn't really seem to work for me, but maybe I misunderstand it.  I was trying to append an ID to a string every time another student was selected, and then like you say compare the list or chosen names to the new choice, with a filter 'contains'.   I do have unique ID's for each student.  

How do I get the future hidden field to contain more than 1 student?  Would this be a list smartbox object that I temporarily create rows and delete rows for each form use?  Seems like unclean as you suggest, but I am looking for 'Any' solution before having an elegant solution.

Thanks!

BKlaege

Ok, So set up a hidden data field to hold your concatenated list of selected student Ids. When the drop-down list is changed, use the transfer data to set the idden data field equal to itself and the dropdown list (just drag the data field and then the auto complete into the transfer data field). This will concatenate the selections into your data field. Then, use the data field as a filter on your dropdown where your hidden data label contains the student Id

[EDIT] Sorry, hold off on that, I have your logic backwards. Let me see if I can reproduce it better

I did not get the concatenate to work yet.

Do you run through a loop of all the items in the list to add them to one field?

I am going to be leaving for the weekend soon, but I do really look forward to this puzzle being solved.

BKlaege

The best solution I can think of at the moment is another table to track the student Ids that have been "selected".

Then, your stored procedure that returns the list of students would join to this table and exclude any students that exist in it.

The only time I have used the table control has been to hold a button control in a certain cell on a form.

I will read up what I can Monday.

It sounds more viable than writing rows to a list view and looping through a compare, only to delete the rows in the list view when the form is submitted.

K2 is such an interesting beast...

Have a good weekend!

BKlaege

My apologies, I didn't mean a table control but a database (SQL) table to track the students that have been selected.

TByrne777,

Happy Monday!

Maybe I can revisit your first suggestion, since the SQL table seems almost the same.

Right now, I don't know how to incorporate SQL into the K2 form, or how to add to a table when each individual student is selected.    I will need the details, but maybe I am just overcomplicating this.  I am familiar with Select statements; updates.  I have used SQL statements within programs, but not on K2.  This would have to be a temp table of some sort, only used by one instance of a form.

Background:  We have an instructor using a form.  We use his unique email address from current user.  An instructor can have multiple courses and sections, so we provide a list of courses for the instructor within a semester and populate a course drop down.  The data we are getting to populate smartbox objects is coming from stored procedures.  This is black box to us.  We provide input; for example Instructor, and Term, and we get back a list of courses.  The instructor selects a course, and then we populate a student dropdown with the students for the selected course.  This is through another smartbox object.   When the instructor choses a student, we add their data to a smartbox object we define in K2, in a list view with a transfer data.  When the instructor is done and selects the submit button, we create and save the form data, and we loop through the student data and create this data as well in a smartbox object.

We are trying to make a recursive routine so that when an instructor chooses a student, the student dropdown is repopulated without the student already selected.  For example, if we have Amy, Bill, and John as students.  The student list would contain these 3 names for first selection.  The second time the dropdown for students to select from would contain the 2 remaining names which were not selected.   Then there would be 1.  Students could be selected in any order.

I'm trying to find out what fields I can compare in an advanced condition.  I am looking in Add/Edit row details, and Display row, but I am unable to see any data within a show statement.  If I can't repopulate a list with less students, then maybe I can find something to compare to; so as not to allow a duplicate suggestion.

Looking for a way.

Thanks!

BKlaege

Happy Monday! I think any way you do this, whether you validate on selection of a student to see whether they've been selected before or try to filter out students that have already been selected you will have to store the selected students somewhere.

To interface with SQL on a form, you would need to register your SQL database as a service instance (I use the SmartObject Service tester for this), and then all of your tables, views and stored procedure in the database become available to wrap in SmartObjects. So it isn't exactly executing SQL, but being able to get/set data from tables and invoke Stored Procedures which should give you the tools to accomplish what you are setting out to do.

Tbyrne777,

I can agree with you.

However, I don't know of how to use K2 for a solution to this, and I have to present the form within K2.

I will keep trying, but I am not understanding your suggestions.

I don't know what you are referring to with the smartobject service tester.  Is this on K2 5.1?  I may have this confused with the smartbox testing client we used with K2 4.7

I could theoretically write a row to a smartbox object when they choose a student and then read this list for compare.  We are back to an ugly solution, but I am looking to get something to work.

There seems to be no way to build a concatenated list (string?) of choices made?  This would be a hidden field on a view.

Thanks for help!

BKlaege

I believe you can also use the K2 Management site to register your SQL database as a service endpoint, though you can also just use another smartbox object to store the selected employees.

Earlier on this thread I did present a way to concatenate the selections, but I think the harder part is actually consuming that list of concatenated values within K2.

the part previously was 'Hold off on that'?

I can transfer data, but maybe you are stating the hard part is to compare to that?

Sorry, but this is confusing.

Yes, you are correct on both fronts - It is possible to concatenate the selected values but then using it for some sort of comparison was not possible so I specified that you should hold off on that so you don't waste your time. And yes, this thread is getting confusing (sorry!) it is a difficult question to resolve through the forums but I'll keep trying!

TByrne777,

Hi.  I have saved the list of choices in a view which I can hide.

I am going to look at expressions to see if I can find something to compare against.  I hope to at least through a message that a duplicate is chosen if I can eliminate it from the dropdown. 

It seems like I need another field to filter on.

Thanks!

BKlaege

I wasn't able to figure out a way to do this in the limited time I have, if you do find something please post it here for everyone!

I am getting close to something workable, and I am going to also quit working on this.  Maybe someone can improve upon it.

I created an item view to just keep a couple of fields.  My first K2 difficulty is that my dropdown is on a list view, so I cannot just drag a couple of fields there.  I also put the views on a form, so I have view rules for the dropdown, and form rules for comparing.  I realize I could put a couple of hidden fields on the form also.  

So, when I choose a name in the dropdown on the view, I use transfer data to build a field on a different view of all choices made.   I also created another field for an expression using 'Contains'.  I add a rule to the form that checks when the dropdown changes.  If advanced condition of the string of choices made contains the add/edit row choice being made, the expression is true.  I just show a message now, that there is a duplicate.

Because of the view and form rules the way I have them, I still add the duplicate to the list, since my evaluation if after all the view rules execute.  You cannot move view rules within a form, and I cannot place the advanced condition exactly where it is needed.  This part needs more tweaking.

As Tbyrne777 stated much earlier, it would be much better to be able to update your smartbox object and then repopulate your list (filtered).   A filter would be way easier.  It would also be a little easier if there was a 'Does Not Contain' compare, so that I would not have to use a separate True/False expression.