Need some help

[jnichols]

Ok I have a form that is used to enter delivery information such as invoice #, name, address, city, state, zip, phone, instructions, order amount and other things. What I want to do, if possible, is when the info is saved to the database, give the delivery a custom unique identity based on the city and a number starting at 1 (i.e. New Orleans - 1, New Orleans - 2, New Orelans - 3 Kenner - 1 Kenner - 2, Kenner - 3, Metairie - 1, Metairie - 2, Metairie - 3)

Thanks in advance,
John

[Shaggy Hiker]

My first thought was autonumber, but obviously that won't do.

It seems to me that for this to work you will need to track the highest used number for each city. That being the case, the obvious way to do so would be to add a table to the database (depending on your design, this may be as simple as adding a field, if the cities are in a table already) that keeps the max number for each city.

I would want to encapsulate the number recovery in a function or two. Perhaps a SaveMaxNumber(cityName as string) and GetMaxNumber(cityName as string) as integer

[ntg]

If you're talking about SQL Server, you can do it there using user-defined functions. You can even bind one to the default value of a unique table key.

[jnichols]

First of all, Sorry, should have been more specific of the database type.

The User Defined funtion method might work, only problem is I don't know how to code it. Can you give me a few pointers on how to go about doing this?

Thanks
John

[ntg]

Attached script creates a table, udf and a trigger. Combined, when you insert something into the table the trigger discards any value on the key (this could be your identity), calculates the key based on field1 and field2 and inserts the new value into the table. Key calculation is inside the user-defined function.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[newTrigger]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[newTrigger]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestFunc]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[TestFunc]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE FUNCTION dbo.TestFunc
(@parm1 nvarchar(10), @parm2 nvarchar(10)) 
RETURNS nvarchar(20)

AS  

BEGIN 

DECLARE @retVal nvarchar(20)
SET @retVal = @parm1 + @parm2
RETURN @retVal

END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE TABLE [dbo].[Table1] (
	[field1] [nvarchar] (10) COLLATE Greek_CI_AS NOT NULL ,
	[field2] [nvarchar] (10) COLLATE Greek_CI_AS NOT NULL ,
	[keyField] [nvarchar] (20) COLLATE Greek_CI_AS NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD 
	CONSTRAINT [PK_Table1] PRIMARY KEY  CLUSTERED 
	(
		[keyField]
	)  ON [PRIMARY] 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER newTrigger ON [dbo].[Table1] 
INSTEAD OF INSERT
AS

BEGIN

   INSERT INTO Table1
	SELECT field1, field2, dbo.TestFunc(field1, field2) FROM INSERTED

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

All the logic necessary to create the unique identifier can be placed into the user-defined function. I think that you can also access other tables from inside that function.