Sorting Form Lookup

replied on March 4, 2020

Hi Tiffany

If you are using the Forms Table Lookup, then you can only choose the sort order where you configured the lookup

If you were using a Stored Procedure then the data would be returned in the order determined by the SQL Stored Procedure programming

0 0

replied on March 4, 2020

Thank you!

Stored procedures are new to me. I have this for my view.

SELECT TOP (100) PERCENT [Active OT], [Serving District], School, [Last Name], FY, [OT Full Name], [COTA Full Name], StudentNIAID, [First Name], [Date of Birth], [Grade New], [OT Direct Min],
[OT Direct Min Per], [OT Consult Min], [OT Consult Min Per]
FROM dbo.[dbo_tbl-Therapy Services SQL Update]
WHERE ([Active OT] = 1)
ORDER BY [Serving District], School, [Last Name]

I'm trying to create a stored procedure

CREATE Procedure AllOTStudents
AS
Select *From [dbo_tbl-Therapy Services SQL Update]
WHERE ([Active OT] = 1)
ORDER BY [Serving District], School, [Last Name]
Go

And to execute, I have this.

Execute AllOTStudents Go

I get the query to run, but it is not sorted. Do you know what I'm missing?

Thanks!

0 0

replied on March 4, 2020

I got the Create Procedure and Execute queries to run. I think I might be missing parameters. I need the collection to populate the students that are assigned to each therapist/assistant combination.

CREATE Procedure AllActiveOTStudentsEOY1
 
AS 
Select *From [dbo_tbl-Therapy Services SQL Update] 
WHERE     ([Active OT] = 1) 
ORDER BY [Serving District]Asc, School Asc, [Last Name]ASC
Go

Execute AllActiveOTStudentsEOY1;  
GO

With added parameters

CREATE Procedure AllActiveOTStudentsEOY2
 @OTFullName nvarchar(255),   
    @COTAFullName nvarchar(255)  
AS 
Select *From [dbo_tbl-Therapy Services SQL Update] 
WHERE     ([Active OT] = 1) and [OT Full Name]=@OTFullName and [COTA Full Name]=@COTAFullName
ORDER BY [Serving District]Asc, School Asc, [Last Name]ASC
Go

This is the error I receive when I run the Execute Query

Msg 201, Level 16, State 4, Procedure AllActiveOTStudentsEOY2, Line 0
Procedure or function 'AllActiveOTStudentsEOY2' expects parameter '@OTFullName', which was not supplied.

Thanks again!

0 0

replied on March 5, 2020 • Show version history

I'm not great with Stored Procedures either. From examples I've seen try adding the brackets I have in bold

CREATE Procedure AllActiveOTStudentsEOY2 (
@OTFullName nvarchar(255),
@COTAFullName nvarchar(255)
) AS

I was using this as reference

https://www.sqlservertutorial.net/sql-server-stored-procedures/stored-procedure-output-parameters/

0 0

replied on March 5, 2020

If you use variables in the Query, you need to provide those variables with values below is a Stored Procedure we run often where @vendorid and @ponumber are passed in through forms.

The look up rule then looks something like this:

Where vendorid and ponumber are automatically pulled into the lookup rule for you to fix values to. Make sure you are updating your data sources in forms as well to keep current with the changing stored procedure.

ALTER PROCEDURE [dbo].[getReceipts]
    -- Add the parameters for the stored procedure here
    @vendorid varchar(50),
    @ponumber 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
    IF(@ponumber is NULL OR @ponumber = '')
        SELECT * FROM DISPLAY_Receipts WHERE @vendorid = vendorid
    ELSE
        SELECT * FROM DISPLAY_Receipts WHERE @vendorid = vendorid AND @ponumber = ponumber
END

Question

Question

Replies

Sign in to reply to this post.