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
Solved! Go 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?
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.
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
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,
FROM Database.[dbo].[Tabelname] AS S
WHERE S.Code = @Code & S.Step - @Step
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
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.
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.
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.
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.