So here's my challemge...

[Ian Branch 127]

Hi Team,
I need a mechanism/code where a 3 letter Company code is combined with an autoinc number, but, the autoinc number is related to the Company code.  The combined are to fit in a 10 char field.  These are to be individual Company Asset Numbers to go on to QRCode labels and into the table.
To clarify:
Let's say there are 3 companies, there will be lots more in practice, with Company codes of ABC, DEF, and GHI.
I need to be able to generate as/when required, and sequentially, ABC0000001 to ABC9999999, and DEF0000001 to DEF9999999, and GHI0000001 to GHI9999999, etc, on an as needed basis.

Thoughts/suggestions appreciated.

Regards & TIA,
Ian

[FPiette 383]

If you have a table with your numbered company code, it is easy to find the max number has been allocated for a given company code. Isn't it?

What kind of storage do you use? SQL database or other?

[Ian Branch 127]

Hi FPiette,

I haven't decided which way to go yet.  Still considering options/alternatives.

 

Ian

[FPiette 383]

Depending on the rest of the application, I would go for SQLite or a full blown MS-SQL Server database.

[PeterBelow 238]

4 hours ago, Ian Branch said:

Hi Team,
I need a mechanism/code where a 3 letter Company code is combined with an autoinc number, but, the autoinc number is related to the Company code.  The combined are to fit in a 10 char field.  These are to be individual Company Asset Numbers to go on to QRCode labels and into the table.
To clarify:
Let's say there are 3 companies, there will be lots more in practice, with Company codes of ABC, DEF, and GHI.
I need to be able to generate as/when required, and sequentially, ABC0000001 to ABC9999999, and DEF0000001 to DEF9999999, and GHI0000001 to GHI9999999, etc, on an as needed basis.

Thoughts/suggestions appreciated.

Regards & TIA,
Ian

Is this for a multi-user application, where several users may request a new code/number combination for the same code concurrently? That may pose a problem unless you can figure out  a way to avoid or at least detect collisions. On a database level sequences (or generators, nomenclature differs between databases) were introduced to solve such issues, but in your case you would need one sequence per code. A unique index on the table storing the code+number values would at least give you a way to detect collisions if you simply use a SELECT max(value) FROM <table> WHERE value like :code+'%' to get the last value used.

[Ian Branch 127]

Hi Team,

Thank you for your inputs.  I have slept on it and come up with using an AssetKeys table.  Two fields - CoyCode - Char 3 and AssetNo - Integer.

The Char field will hold the Company code and the Integer field will hold the next Asset # to use.  The CoyCode field will have a unique index.

When a new Company is added the AssetKeys table will have the new Coy code added and the AssetNo field set/used.

The AssetKeys table will be linked to the Assets table via the CoyCode. 

When a new asset is added the, current AssetNo will be retrieved and then incremented in AssetKeys.

The CoyCode and retrieved AssetNo will then be manipulated to give a 10 char Asset # per my examples above.

Here is my preliminary code in the BeforePost Event..

  //
  //  Get current Asset # for the Relevant Company based on the Coy Code.
  AssetKeys.Open;
  if AssetKeys.eof then
    AssetKeys.AppendRecord([CoyAssets.FieldByName('CoyCode').AsString]); //  AssetKeys.AssetNo defaults to 1 on a new record.
  iAssetNo := AssetKeys.FieldByName('AssetNo').AsInteger;
  AssetKeys.Edit;
  AssetKeys.FieldByName('AssetNo').AsInteger := AssetKeys.FieldByName('AssetNo').AsInteger + 1;
  AssetKeys.Post;
  AssetKeys.Close;
  //
  //  Now create the actual Assset #.
  sAssetNo := IntToStr(iAssetNo);
  sAssetNo := sAssetNo.PadLeft(7, '0');
  sAssetNo := CoyAssets.FieldByName('CoyCode').AsString + sAssetNo;
  //
  // set the CoyAssets Asset #.
  CoyAssets.FieldByName('AssetNo').AsString := sAssetNo;
  //

Ian

Edited April 4, 2022 by Ian Branch

[Lars Fosdal 1792]

Doesn't this mean that if you have two devices accessing the same database, they can create duplicate AssetKeys?
 

I prefer to use auto incremented ID fields in the database.
If I was being security minded - I would also add GUID identities per row so that it would be much harder to abuse Id values in illegitimate queries if the keys are exposed in a web or REST UI.

 

MS SQL example

CREATE TABLE [dbo].[t_company_code](
	[CoyId] [int] IDENTITY(1,1) NOT NULL,
	[CoyCode] [nchar](3) NOT NULL
	[CoyCreated] [datetime] NOT NULL
) ON [PRIMARY];
GO
ALTER TABLE [dbo].[t_company_code] ADD  CONSTRAINT [DF_t_company_code_Created]  DEFAULT (getdate()) FOR [CoyCreated];
GO

CREATE TABLE [dbo].[t_assets](
	[AssetId] [int] IDENTITY(1,1) NOT NULL,
	[CoyId] [int] NOT NULL,
    [Description] [nchar](63), 
	[AssetCreated] [datetime] NOT NULL
) ON [PRIMARY];
GO
ALTER TABLE [dbo].[t_assets] ADD  CONSTRAINT [DF_t_Asset_Created]  DEFAULT (getdate()) FOR [AssetCreated];
GO

-- You would then use a view to join them 
CREATE VIEW [dbo].[v_CoyAssets]
AS
SELECT dbo.t_company_code.CoyId, dbo.t_company_code.CoyCode, dbo.t_company_code.CoyCreated, 
  dbo.t_assets.AssetId, dbo.t_assets.Description, dbo.t_assets.AssetCreated
FROM dbo.t_company_code INNER JOIN dbo.t_assets ON dbo.t_company_code.CoyId = dbo.t_assets.CoyId;
GO

and do a select from the view

SELECT CoyCode, AssetId from v_CoyAssets order by CoyCode, AssetId;

 

[Ian Branch 127]

Hi Lars,

Thank you for your input.

In reality the maintenance (editing/adding) of assets is vested in one person, the Storeperson, so there won't be any clash. 

Security is not a big issue in this environment.

The action of my code above is almost instantaneous however I am still considering if I should put it in a transaction, or not.  Just for the added peace of mind.

 

Ian

[Fr0sT.Brutal 900]

30 minutes ago, Lars Fosdal said:

Doesn't this mean that if you have two devices accessing the same database, they can create duplicate AssetKeys?
 

I prefer to use auto incremented ID fields in the database.

1) UNIQUE constraint on both fields could solve this

2) Using Autoinc there could be only one for all companies so there will be holes in numbering and number capacity could exhaust very quickly (10E8-1). Splitting area between several autoincs could push the issue into more far future but it would make things complicated

[Lars Fosdal 1792]

@Fr0sT.Brutal Valid point on the auto increment. 

[FPiette 383]

In a multi-user environment, to increment CoyId, a transaction should be used along with SELECT FOR UPDATE statement and an UPDATE statement. Other database engine may use other syntax.