You are viewing limited content. For full access, please sign in.

Question

Question

Find most recent SQL record for lookup

Forms Workflow
Updated August 31, 2018
asked on August 30, 2018 • Show version history

I am tracking how many times visitors visit an office. Each guest visit is inserted into a SQL table. Therefore, many visits may be attributed to the same guest, resulting in many rows for that particular "user id." Is there a way in forms (or workflow) to find the most recent guest record in order to correctly populate forms fields with the most up-to-date guest information? Using Workflow 10.2, Forms 10.2.

Thank you.

0 0

Answer

SELECTED ANSWER
replied on August 31, 2018

Probably easiest to build a view:

CREATE VIEW Visit_Lookup AS
SELECT
	Guest
	,GuestInfo
	,Visits
	,LastVisitDateTime = VisitDateTime
	FROM (
		SELECT
			Guest
			,GuestInfo
			,VisitDateTime
			,Visits = COUNT(*) OVER(PARTITION BY Guest)
			,rn = ROW_NUMBER() OVER(PARTITION BY Guest ORDER BY VisitDateTime DESC)
			FROM Visit
		) x
	WHERE rn = 1
	;

 

0 0
replied on August 31, 2018

Perfect, thanks!

0 0

Replies

replied on August 30, 2018

In your Table that holds these records, is there a primary key field (unique incrementing number)?  If so than you can do something like this:

SELECT TOP 1 * FROM TableName
WHERE Condition = Value
ORDER BY KeyColumn DESC;

 

2 0
replied on August 31, 2018

Thanks for your response

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...