topic Re: Autonumber in App Development

Autonumber

Anonymous — Fri, 20 Oct 2017 10:15:10 GMT

I am using Autonumber to rank several fields but want to reset the Autonumber back to 1.

If I load a table as below set an autonumber and then drop the PKRowNoTT field to use it again should this not reset the autonumber back to 0 ? if not can it be ?

Sub TeacherTargetData

//I load a table from SQL Server and set the first autonumber field

LIB CONNECT TO 'ProgressTransitions';
NoConcatenate
TeacherTargets:
LOAD Distinct
      "Primary(TT)",
      "Result(TT)",
      "Year(TT)",
      "Subject(TT)",
      "Type(TT)",
      "Area(TT)",
      "Basket(TT)",
      "Key",
      AutoNumber("Result(TT)"&"Subject(TT)","Year(TT)"&"Primary(TT)"&"Basket(TT)") As PKRowNoTT,
      "Primary(TT)"&"Year(TT)" As TA8;
SQL SELECT "Primary(TT)",
      "Result(TT)",
      "Year(TT)",
      "Subject(TT)",
      "Type(TT)",
      "Area(TT)",
      "Basket(TT)",
      "Key"
FROM ProgressTransitions.dbo."view_teachertargets"
Order by "Result(TT)" Desc,"Year(TT)","Primary(TT)","Subject(TT)","Basket(TT)" Desc;

DisConnect;

//I use the PKRowNoTT to filter out records to be used in the next stage

Left Join(TeacherTargets)

Load Key,

"Result(TT)" As tmpB4TT

Resident TeacherTargets

Where (("Basket(TT)"=4)

or ("Basket(TT)"=1 And PKRowNoTT>1)

or ("Basket(TT)"=2 And PKRowNoTT>1)

or ("Basket(TT)"=3 And PKRowNoTT>3));

//I drop the field to use it again

Drop Field PKRowNoTT;

//I join the table back on to its self but with a new autonumber to allow me to filter out the records

Left Join(TeacherTargets)

Load

AutoNumber("tmpB4TT"&"Subject(TT)","Year(TT)"&"Primary(TT)"&"Basket(TT)") As PKRowNoTT

Resident TeacherTargets

Where tmpB4TT >=0.00

Order by "tmpB4TT" Desc,"Year(TT)","Primary(TT)","Subject(TT)","Basket(TT)" Desc;

End Sub

Re: Autonumber

sunny_talwar — Fri, 20 Oct 2017 12:09:59 GMT

Try this

Sub TeacherTargetData

//I load a table from SQL Server and set the first autonumber field

LIB CONNECT TO 'ProgressTransitions';
NoConcatenate
TeacherTargets:
LOAD Distinct
      "Primary(TT)",
      "Result(TT)",
      "Year(TT)",
      "Subject(TT)",
      "Type(TT)",
      "Area(TT)",
      "Basket(TT)",
      "Key",
      AutoNumber("Result(TT)"&"Subject(TT)","Year(TT)"&"Primary(TT)"&"Basket(TT)", 'ID1') As PKRowNoTT,
      "Primary(TT)"&"Year(TT)" As TA8;
SQL SELECT "Primary(TT)",
      "Result(TT)",
      "Year(TT)",
      "Subject(TT)",
      "Type(TT)",
      "Area(TT)",
      "Basket(TT)",
      "Key"
FROM ProgressTransitions.dbo."view_teachertargets"
Order by "Result(TT)" Desc,"Year(TT)","Primary(TT)","Subject(TT)","Basket(TT)" Desc;

DisConnect;

//I use the PKRowNoTT to filter out records to be used in the next stage

Left Join(TeacherTargets)

Load Key,

"Result(TT)" As tmpB4TT

Resident TeacherTargets

Where (("Basket(TT)"=4)

or ("Basket(TT)"=1 And PKRowNoTT>1)

or ("Basket(TT)"=2 And PKRowNoTT>1)

or ("Basket(TT)"=3 And PKRowNoTT>3));

//I drop the field to use it again

Drop Field PKRowNoTT;

//I join the table back on to its self but with a new autonumber to allow me to filter out the records

Left Join(TeacherTargets)

Load

AutoNumber("tmpB4TT"&"Subject(TT)","Year(TT)"&"Primary(TT)"&"Basket(TT)", 'ID2') As PKRowNoTT

Resident TeacherTargets

Where tmpB4TT >=0.00

Order by "tmpB4TT" Desc,"Year(TT)","Primary(TT)","Subject(TT)","Basket(TT)" Desc;

End Sub

Re: Autonumber

Anonymous — Fri, 20 Oct 2017 12:48:42 GMT

Hi Sunny,

When I put the code it throws an error (see attached screen shot)

Thanks

Chris

Re: Autonumber

sunny_talwar — Fri, 20 Oct 2017 12:54:06 GMT

Can you change this comma to a &

Re: Autonumber

Anonymous — Fri, 20 Oct 2017 12:58:40 GMT

Yes, it works

I have added it to all the autonumbers in several sub routines I have and used a different number i.e ID1 ID2 ID3 etc.

Fantastic, been at this for 2 days.

Many Thanks

Chris

Re: Autonumber

jonathandienst — Fri, 20 Oct 2017 13:00:02 GMT

Perhaps this is what you need for the two autonumber expressions?

AutoNumber("Result(TT)" & "Subject(TT)", '1-' & "Year(TT)" & "Primary(TT)" & "Basket(TT)") As PKRowNoTT,

AutoNumber("tmpB4TT" & "Subject(TT)", '2-' & "Year(TT)" & "Primary(TT)" & "Basket(TT)") As PKRowNoTT