cancel
Showing results for 
Search instead for 
Did you mean: 
brianknight
Nintex Newbie

Additional help with the Sql Control

Jump to solution

I have a question regarding the sql control.  Is it possible to display the unduplicated list of potential choices.  Let me show you what I’m talking about.  This is the returned value set returned from a sql query

We have several Pay schedules each with multiple steps (I know only government can create such a goofy system).  The database looks like this:

Is there a way to just limit the view to do the following?

My control is setup is this

Ultimately, I'll need to limit the Step query to list the steps based on what was selected in this control.  From there I can capture the rate to determine what the salary will be.  Eric Harris

0 Kudos
Reply
8 Replies
Highlighted
Automation Master
Automation Master

Re: Additional help with the Sql Control

Jump to solution

I'm not quite sure what exactly you'd need.

would you like to appear each Code just once int he list?

how does steps relate to this? (" I'll need to limit the Step query...")

why do you select for other for other columns (step, description,....) apart from code column?

0 Kudos
Reply
brianknight
Nintex Newbie

Re: Additional help with the Sql Control

Jump to solution

Correct, I'll need the Code to only appear once.  Then on another Sql control, I'll need to select the step that corresponds with the Code.  For example code M1 with step 5 will give a rate of 3753.33.  Essentially people will choose a Code, then a step.  In most cases, there are 20 steps for each Code.  hose two values give you the rate.  I hope this makes sense.  

0 Kudos
Reply
Stonehage
Nintex Newbie

Re: Additional help with the Sql Control

Jump to solution

Select Distinct Code as Code From Table name should do that.

Then you'll need to pass that parameter ( M1)  into a Stored Procedure, to another control to get your steps. Something like..

CREATE PROCEDURE [dbo].[SSP_Lookup_Step]

@Code varchar(100) = null

AS

 

SELECT S.Step

FROM Database.[dbo].[Tabelname] AS S

WHERE S.Code = @Code

Then another stored procedure passing the two chosen parameters to extract the relevant rate.

CREATE PROCEDURE [dbo].[SSP_Lookup_Step_Rate]

@Code varchar(100) = null,

@Step Int

AS

 

SELECT S.Rate

FROM Database.[dbo].[Tabelname] AS S

WHERE S.Code = @Code & S.Step - @Step

T.

Reply
brianknight
Nintex Newbie

Re: Additional help with the Sql Control

Jump to solution

Ok, though I seem to be having a challenge passing the parameter of the code on to the next control.  I had a thought of maybe trying the following:

Select [step] ,[steprate] from [vwpayinformation] where

 = PaySchedule (the name of the Code control). 

Unfortunately it does not seem to work.  I receive no options in the drop down.  When I insert say 'M1' in place of the control name, I do get the correct items. It seems like I should be able to use the name of a control, but maybe I'm missing something here?  Here is my control settings now

0 Kudos
Reply
Stonehage
Nintex Newbie

Re: Additional help with the Sql Control

Jump to solution

Make sure you're using Named Controls not Item Properties when inserting refrences. If that's not working try making a calculated field to point to your choice field and then use that calculated column as the parameter to pass. This has the advantage of you being able to check what's being passed as well.

0 Kudos
Reply
brianknight
Nintex Newbie

Re: Additional help with the Sql Control

Jump to solution

I noticed something.  I removed the space in between the "=" and the control name of "PaySchedule".  When I did that and did a test, I received an error of Invalid column name 'PaySchedule'.  I thought you could use control names as part of the query.  I consequently did the same by creating a calculated field and referencing that from the query.

0 Kudos
Reply
brianknight
Nintex Newbie

Re: Additional help with the Sql Control

Jump to solution

OK solved the issue with the second control.  I was missing the ' ' between my control name.  Found the answer by looking at this blog post Nintex Forms Enterprise - SQL Request and Web Service Request enhancement.  So here is the correct control property setting.

Now on to the final control.  

0 Kudos
Reply
brianknight
Nintex Newbie

Re: Additional help with the Sql Control

Jump to solution

Ok everyone, the controls are all set and I was able to capture the correct salary data into a calculated field.  I'll show everyone the control properties in case you are interested.  The first control is the pay schedule code.  Each code can have multiple steps.  

After you choose a code, you now need to choose a step.  We altered our reporting database to create a more user friendly field that shows the step along with the bi-weekly pay.  

The value field from this selection is captured into the control and will be passed on down to a Calculated control.  

We had to parse the value as it was adding additional characters we didn't need.  We then multiply the rate by 26 to obtain the annual salary and viola everything is working. Thanks to all who helped (Anthony Parker‌ and Marian Hatala‌) with getting this working.  

View solution in original post

Reply