AnsweredAssumed Answered

Populate field with matching values

Question asked by madmike6537 on Jul 18, 2016
Latest reply on Jul 18, 2016 by Johan Hedman

Edit: It seems its not working because my matching field is based on the NAME field which is text. If I change the name to a number, it works. Hmm. I would have thought it would match fine with text.


Hi all,


Back with a question. I have a layout on which our users will be creating new records in the CUSTOMER table. We are trying to prevent entering duplicate records, meaning if a customer hires us a second time, they need to create a new PROJECT, not create the customer again. Currently, we just run a little script after the name is entered and notify the user if the name is already in use. I am trying to make the solution a little more elegant by showing the user possible duplicate records.


I am open to anyway to do this, but my thought was to use one of my generic global fields as a drop down and populate it with a value list of matching records along with the associated addresses. But I cant get it to work. What I have done is on my customer table, I added a second T.O. called customer_CUSTOMER and matched them on the NAME field. Then, I created a value list that pulls values from this table based on the relationship, hoping it would populate my field with any records matching the name, but it just shows up emtpy in testing.


My layout is based on the base CUSTOMER table, so I cant use a portal. I could use my preferences table as the context, but then all my customers would have to be related to it, which I am tying to avoid.


I also thought about using ExecuteSQL, but I would like the user to be able to select the duplicate record with a click and keep going, rather than have to do a search for it.


Any thoughts?