Question

Forms Help

Forms

Updated March 7, 2017

asked on February 27, 2017

Dear All,

I need some help re a scenario:

So the issue is like this:

1. There are users in SQL (with short name e.g. sas, asd, dsa) these are 3 letter names or some are 2 letters, but never more than 3.

2. In the form there are 2 fields : First Name & Last Name

3. Based on the First & Last Name the short name is to be generated or suggested

4. The suggestion should be checked with existing short names & if exist then based on the rule (as below 5 rules) should suggest the new short name.

Rules for short name creation:

Example: Max Muster

Rule 1: 1st letter of the first name, 1st + 2nd. Letter of surname: mmu

If short-name already exists, the following rules come into effect:

Rule 2: 1. + 2. Letter of first name, 1st letter of surname: mam

Rule 3: 1. + 2. + 3. Letter of surname: mus

Rule 4: 1st letter of the first name, 1st + last letter of the last name: mmr

Rule 5: 1. + last letter of first name, 1st letter of last name: mxm

The new suggested name should be populated in the short name field so the user can actually see it.

Thanks in advance for your help!!!

Regards,

Sahil

0 0

Answer

SELECTED ANSWER

replied on February 27, 2017 • Show version history

I threw together a quick stored procedure that combines all five rules and returns the first available option. The name of the table where I stored the "Existing" short names was short_name_test (so you'll want to change that to your real table name in all the join statements at the bottom). The name of the value in that table with the short name value was short_name (so you'll want to change that to your real field name in all the join statements at the bottom and the select case statement).

Good luck!

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            Matthew Tingey
-- Create date:       2/27/2017
-- Description:       Test of a Stored Procedure to Return a 3-digit "short name" based on submitted first and last name values
-- =============================================
CREATE PROCEDURE GetShortName 
	-- Add the parameters for the stored procedure here
	@FirstName VARCHAR(50), 
	@LastName VARCHAR(50) 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	
  SELECT 
    CASE 
      WHEN rule1.short_name IS NULL THEN subquery.rule1 
      WHEN rule2.short_name IS NULL THEN subquery.rule2 
      WHEN rule3.short_name IS NULL THEN subquery.rule3 
      WHEN rule4.short_name IS NULL THEN subquery.rule4 
      WHEN rule5.short_name IS NULL THEN subquery.rule5 
      ELSE 'No Options' 
    END AS return_value  
  FROM (
    SELECT 
    LOWER(CONCAT(LEFT(@FirstName, 1), LEFT(@LastName, 2))) AS rule1,  
    LOWER(CONCAT(LEFT(@FirstName, 2), LEFT(@LastName, 1))) AS rule2, 
    LOWER(LEFT(@LastName, 3)) AS rule3, 
    LOWER(CONCAT(LEFT(@FirstName, 1), LEFT(@LastName, 1), RIGHT(@LastName, 1))) AS rule4, 
    LOWER(CONCAT(LEFT(@FirstName, 1), RIGHT(@FirstName, 1), LEFT(@LastName, 1))) AS rule5
    ) AS subquery 
  LEFT JOIN short_name_test AS rule1 ON rule1.short_name = subquery.rule1 
  LEFT JOIN short_name_test AS rule2 ON rule2.short_name = subquery.rule2 
  LEFT JOIN short_name_test AS rule3 ON rule3.short_name = subquery.rule3 
  LEFT JOIN short_name_test AS rule4 ON rule4.short_name = subquery.rule4 
  LEFT JOIN short_name_test AS rule5 ON rule5.short_name = subquery.rule5 

END
GO

1 0

Replies

replied on March 7, 2017

I figured it out, Thanks.

1 0

replied on February 27, 2017

I would do this using stored procedures in your database. Because of the lookups necessarily to compare against your database, trying to do this without using a stored procedure, will be difficult.

You could make five different stored procedures, one for each rule, returning either the result or an error if that resulting value already exists in the database, and populate them to five hidden fields, and evaluate them inside your form using formulas or Javascript.

Or, if you are really daring, make one stored procedure that does the whole thing. This will be trickier to set-up, but make for a cleaner process inside your form - and will likely process faster.

0 0

replied on February 28, 2017

Mathew,

WOW! Awesome!

Thanks a lot!

I got another question or request:

I have some names with Special Characters which in another Powershell script I replaced like:

"Ä", "Ae"
   "Ö", "Oe"
   "Ü", "Ue"
   "ä", "ae"
   "ö", "oe"
   "ü", "ue"
   "ß", "ss"
   "É", "e"
   "È", "e"
   "é", "e"
   "è", "e"
   "Á", "a"
   "À", "a"
   "Â", "a"
   "á", "a"
   "à", "a"
   "â", "a"
   " ", ""

Is there a way to build it in Stored procedure?

Regards,

Sahil

0 0

replied on February 28, 2017

Yes, that should be very doable with SQL's REPLACE function.

Syntax: REPLACE(@Name, "Ä", "Ae")

I'm not near my computer where I can write out a more detailed response or run some tests, but I've used REPLACE lots of times before.

Since you have a long list of items to replace, you'll probably want to do a bunch of nested REPLACE functions.

REPLACE(REPLACE (REPLACE(@Name, "Ä", "Ae"), "Ö", "Oe"), "Ü", "Ue") etc. etc.

It's not the only way to do it, but it should work.

0 0

replied on February 28, 2017

Mathew,

Thanks for the help, is it too much to ask to send the whole code, when you have time?

I'm not very proficient with SQL & am bit hesitant...

You can do when you get time, it's not burning...

Regards,

Sahil

0 0

replied on February 28, 2017

This should work.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            Matthew Tingey
-- Create date:       2/28/2017
-- Description:       Test of a Stored Procedure to convert special characters in a name
-- =============================================
CREATE PROCEDURE RemoveSpecialCharacters 
	-- Add the parameters for the stored procedure here
	@Name VARCHAR(100)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

  SELECT 
    REPLACE( 
      REPLACE( 
        REPLACE( 
          REPLACE( 
            REPLACE( 
              REPLACE( 
                REPLACE( 
                  REPLACE( 
                    REPLACE( 
                      REPLACE( 
                        REPLACE( 
                          REPLACE( 
                            REPLACE( 
                              REPLACE( 
                                REPLACE( 
                                  REPLACE( 
                                    REPLACE( 
                                      REPLACE( 
                                        @Name 
                                      , ' ', '')
                                    , 'ß', 'ss')
                                  , 'â', 'a') 
                                , 'à', 'a') 
                              , 'á', 'a') 
                            , 'è', 'e') 
                          , 'é', 'e') 
                        , 'ü', 'ue') 
                      , 'ö', 'oe') 
                    , 'ä', 'ae') 
                  , 'Ä', 'Ae') 
                , 'Á', 'A') 
              , 'À', 'A') 
            , 'Â', 'A') 
          , 'É', 'E') 
        , 'È', 'E') 
      , 'Ö', 'Oe') 
    , 'Ü', 'Ue') 
  AS newName 

END 
GO

0 0

replied on February 28, 2017

My Apologies for being stupid.... :(

This is the code for me now, can you please tell me where I messed up?

USE [DBA_Utilities]
GO
/****** Object:  StoredProcedure [dbo].[GetShortName]    Script Date: 28.02.2017 17:12:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            Matthew Tingey
-- Create date:       2/27/2017
-- Description:       Test of a Stored Procedure to Return a 3-digit "short name" based on submitted first and last name values
-- =============================================
ALTER PROCEDURE [dbo].[GetShortName] 
	-- Add the parameters for the stored procedure here
	@FirstName VARCHAR(50), 
	@LastName VARCHAR(50) 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT 
    REPLACE( 
      REPLACE( 
        REPLACE( 
          REPLACE( 
            REPLACE( 
              REPLACE( 
                REPLACE( 
                  REPLACE( 
                    REPLACE( 
                      REPLACE( 
                        REPLACE( 
                          REPLACE( 
                            REPLACE( 
                              REPLACE( 
                                REPLACE( 
                                  REPLACE( 
                                    REPLACE( 
                                      REPLACE( 
                                        @Name 
                                      , ' ', '')
                                    , 'ß', 'ss')
                                  , 'â', 'a') 
                                , 'à', 'a') 
                              , 'á', 'a') 
                            , 'è', 'e') 
                          , 'é', 'e') 
                        , 'ü', 'ue') 
                      , 'ö', 'oe') 
                    , 'ä', 'ae') 
                  , 'Ä', 'Ae') 
                , 'Á', 'A') 
              , 'À', 'A') 
            , 'Â', 'A') 
          , 'É', 'E') 
        , 'È', 'E') 
      , 'Ö', 'Oe') 
    , 'Ü', 'Ue') 
  AS newName 

END 
GO 

    -- Insert statements for procedure here
	
  SELECT 
    CASE 
      WHEN rule1.sAMAccountName IS NULL THEN subquery.rule1 
      WHEN rule2.sAMAccountName IS NULL THEN subquery.rule2 
      WHEN rule3.sAMAccountName IS NULL THEN subquery.rule3 
      WHEN rule4.sAMAccountName IS NULL THEN subquery.rule4 
      WHEN rule5.sAMAccountName IS NULL THEN subquery.rule5 
      ELSE 'No Options' 
    END AS return_value  
  FROM (
    SELECT 
    LOWER(CONCAT(LEFT(@FirstName, 1), LEFT(@LastName, 2))) AS rule1,  
    LOWER(CONCAT(LEFT(@FirstName, 2), LEFT(@LastName, 1))) AS rule2, 
    LOWER(LEFT(@LastName, 3)) AS rule3, 
    LOWER(CONCAT(LEFT(@FirstName, 1), LEFT(@LastName, 1), RIGHT(@LastName, 1))) AS rule4, 
    LOWER(CONCAT(LEFT(@FirstName, 1), RIGHT(@FirstName, 1), LEFT(@LastName, 1))) AS rule5
    ) AS subquery 
  LEFT JOIN GetActiveDirectoryUsers AS rule1 ON rule1.sAMAccountName = subquery.rule1 
  LEFT JOIN GetActiveDirectoryUsers AS rule2 ON rule2.sAMAccountName = subquery.rule2 
  LEFT JOIN GetActiveDirectoryUsers AS rule3 ON rule3.sAMAccountName = subquery.rule3 
  LEFT JOIN GetActiveDirectoryUsers AS rule4 ON rule4.sAMAccountName = subquery.rule4 
  LEFT JOIN GetActiveDirectoryUsers AS rule5 ON rule5.sAMAccountName = subquery.rule5 

END

0 0

replied on February 28, 2017

Oh yeah, that's not going to work. You've inadvertently combined both of the stored procedures into one script.

I recommend deleting the stored procedure that was created before, and then running each of the two scripts I provided separately, which should create two separate stored procures.

GetShortName - will take in @FirstName and @LastName and the return value (the short name value) is named return_value

RemoveSpecialCharacters - will take in @Name and the return value (scrubbed of the various characters) is named newName

If you need to run both procedures on a value, I recommend using hidden fields in your form to process them in a couple steps.

0 0

replied on February 28, 2017

Be sure when you mark the post as answered to mark the answer to your original question, not the secondary question.

0 0

replied on March 1, 2017

Awesome Mathew!

Thanks!

0 0

replied on March 1, 2017

You are very welcome.

0 0

replied on March 6, 2017

Mathew,

I got a bit of trouble in the first stored procedure, I wanted that it should check the table if the name exists then go to the second option & so on...

I just ran into one of the name (where because of the german umlaut it makes ä to ae) there it gives the first option, which actually already exists...

Is there a way to check after the german character is translated to check for the name rules?

Thanks in advance!

0 0

You are not allowed to follow up in this post.

Question

Question

Forms Help

Answer

Replies

Sign in to reply to this post.