Jump to content

Flag if any of the related records contain "TEXT"


This topic is 2131 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I apologize but I searched up and down and did my fair share of research but I can't for the life of me figure this one out. 

 

So what I am trying to achieve is something straightforward:

I have a main table, ORDERS, that simply has one record for each order. 

Amongst the fields in ORDERS, I have: 

ORDERS::Client and

ORDERS::Agent. 

Both fields contain Text values; ORDERS::Agent being any one of a set Value list containing my 20 agents. 

ORDERS::Client is whatever the client entered; so the same client may enter his/her name ever so slightly differently. One time, they may enter Mike Miller; the next time around, they may enter M. Miller; and the next time around they may enter Michael Miller - even though it's always the same client. 

--------------------------------------------------------------------------------------------------------------------------

Then, I have a related table called TRUSTED AGENTS. The purpose is simply to route a returning client to the same agent. 

The relationship is ORDERS::Agent = TRUSTED AGENTS::Agent. 

Only fields in that table are TRUSTED AGENTS:Agent and TRUSTED AGENTS::Client. 

For every order, I populate the TRUSTED AGENTS:Agent and TRUSTED AGENTS::Client fields. 

--------------------------------------------------------------------------------------------------------------------------

What I need to achieve is to flag every case where a client was previously handled by one of my agents so I may route to the same Agent. 

The workaround I have now is A Calculation Field called 'ORDERS::TrustedAgentCheck' that is defined as:

If ( Count (TRUSTED AGENTS::Client) > 0 ; ORDERS::Agent ; "NEW CLIENT" )

The amazing thing is this actually works - somewhat. 

----------------------------------------------------------------------------------------

The problem with this approach is it will only match EXACT verbatim matches. If the Client entered his name is M. Miller the first time, it will only match M. Miller the second time, but will miss Michael Miller completely. 

I know you could say well give them a structure that makes it less likely to spell their names differently; but the client names are actually not just typed in by the clients, sometimes they come through third party platforms that format them differently. 

What I need is to manually truncate the TRUSTED AGENTS::Client entry to the smallest possible denominator, if you will. If a client enters his name as Michael J. Miller, Bend, OR - all I want to store in my TRUSTED AGENTS::Client field is 'Miller". That's all I need. 

But how can I achieve a match that I would best describe as: 

If ( ORDERS::Client contains or partially contains any of the records in TRUSTED AGENTS::Client; flag this order) 

 

I am really struggling with this and can't seem to find a viable solution to this. 

 

 

Any help is appreciated!

 

 

Thank you

Link to comment
Share on other sites

If it were me, the first thing I would do is clean up the data to make sure there is only one spelling of each Agents name.  Then when selecting agents, only allow them to enter a name from a picker list/layout.  

Then I think most of your troubles solve themselves..

Edited by Steve Martino
Link to comment
Share on other sites

I agree with Steve here. The problem is that you are allowing free text entry of the client name. This will always cause you a headache.

You need a table of CLIENTS. When you receive an order, direct or from a 3rd Party, and someone needs to associate the order with the correct client. Or if you are allowing clients to enter orders directly into your database, then you need to give them an account number, and they should type that when they enter an order, allowing you to create a 1 to 1 relationship between the order and the client.

There is no way that you will come up with anything reliable if you are trying to 'reduce' whatever was typed down to something that 'might' match another record, and allow your Trusted Agents feature to work.

Sorry if that isn't the answer you were after, but that is kind of how relational databases are designed to work!

Link to comment
Share on other sites

This topic is 2131 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.