Thank you for the quick reply. I can use the serial number field, but I want it to be conditional. Of all the jobs logged, I want it to count up only if it is for sales.
Cheers and thanks
I suggest explaining why you want it to work that way.
I am sorry, I should have explained that to begin with. I am exporting this data to another piece of software which creates a specific report. Unfortunately I am bound to use that software and the catch is that each day this report has to start again with a numbered sequence of 1 to however many sales there are. I can manually type a number in a field and that works, the confusion comes from having more than one operator doing the data entry. I cannot allow a duplication of that number. I also have to keep the sales and services on the same data entry layout, but am trying to separate them to export the data and number them 1-whatever at that point.
It is doing my head in.
food for thought (from a previous post) ...
one part of your solution might include a "public table" - this is a table with a single record and non-global fields for "public" data ... like the current rate of the week. This table is connected to your other table(s) via a cross-join relationship.
All this does for you is allow you to populate a single field (e.g. WeeklyCurrencyRate) which can be accessed by all users, all week. How you go about populating this field is still a question.
I have managed to find away around this. It meant creating a separate layout and and using the Get(RecordNumber) in a script step and not from the Autoenter on record creation.
If possible, avoid incrementing numbers like this -- whether they be for years or departments. It will become a nightmare.
The best solution I have found is to keep the next serial numbers in a table, where each record is for the department (or years). The process then goes:
Go to the department (or year) record
increment the serial number
remember the number
create a new job record
set the serial number
The "increment the serial number" process needs to trap for errors which can happen if somebody else is incrementing the number at the same time. Decide how to handle this -- wait in a loop until the number can be incremented, or end the process and tell the user to try later.
This solution is handy when clients want jobs numbered by year. Inevitably at the start of the new year, some people need to enter some records for last year, and other people need to enter jobs for the new year. This cannot be done with a simple serial number field, the only way is to remember the last number for each year.
The nightmare happens where somebody creates a job for the wrong department, then wants it transferred to the right department, then the other department has a missing job number and we cannot have missing numbers!
That is the best solution. I like the way it works.
1 of 1 people found this helpful
Here is a method for generating serial number "on demand" that does not have an issue with two people trying to do it at the same time and thus getting the same value in two different records:
Define a related table. I'll call it "sales serial numbers". A field in this table will provide the needed unique serial number to the parent table when requested. We'll name that field SalesSerialNumb.
Define the fields in the related table as:
_fkParentID (number field)
SalesSerialNumb (auto-entered serial number)
Define a relationship from your parent table as:
ParentTable::__pkParentID = SalesSerialNumbers::_fkParentID
Allow the "create" option for SalesSerialNumbers in this relationship.
When the user enters sufficient data that the new Parent record is identified as a "sales" record, have a script do this:
Set Field [SalesSerialNumbers::_fkParentID ; ParentTable::__pkParentID ]
This step will create a new record in SalesSerialNumbers each time it is performed for a record in ParentTable that does not already have a related records in SalesSerialNumbers and that event will generate a new, unique serial number each time this happens. You can either just refer to the related field during export or you can use an auto-enter option to copy this value into a field in ParentTable. Set Next Serial Value, is a script step that you can use to reset this serial number field from a script should you need to do so.
This method worked and I think is the cleanest way of doing it.
Thank you for the very clear way of explaining what to do.