I'm not 100% sure I understand your question. What you're trying to do could possibly be done, but I'm really not sure what the end goal would be. Can you explain a bit more what you're trying to accomplish - the reason you're trying to mimic the Find requests as a sort?
You can sort based on a custom order, but it must be based on a value list. If you can make a value list based on a global field that you set at the runtime of your find, then you can sort based on that.
Here's a good article explaining custom order sorting:
For the value list, make a globally stored text field, during your script use a "set field" script step to set that field's value as your sort order (return delimited list). Make a value list based on that globally stored field. Then add a custom sort based on that value list.
Thank you for your reply.
There is a database of thousands of records which are for sale via third party sellers.
When a batch of items from one store are 'sold' a sheet is created using a find request and then new find request to send a receipt for all of these items. An item which was created last in the database could potentially be sold first. The list could have 10 items or more on it. Using Find - new request a 'random' set of records is found. The return order is the order in which they were created in the database not the order the mulitple find request was entered.
It is important that the list which is presented/prepared is the find order.
Do you know if the find new request can be scripted.
Okay, well, the short answer is yes, a Find request can be scripted. But I still don't understand what you're doing.
You can't create a new record using a Find request, so this doesn't make sense:
When a batch of items from one store are 'sold' a sheet is created using a find request
Do you mean you're using a Find request to create a report?
This part does give me a bit of a clue:
An item which was created last in the database could potentially be sold first.
I think what you're trying to do is create an invoice, in which case you'd need to create a separate table and store the primary keys of the items in that table. You can then sort them according to an item or line item number when you create the report.
In other words, don't try to use the catalog of all the items to create your receipts / invoices. Instead, create actual records of invoices (with associated line items) that record (as records in the database) what items were being sold on each transaction. You can then store the appropriate sort order with those line items as part of the creation of the invoice.
To add to Mike's explanations, and put this in a structured form:
You need table and relationships like
Clients --< Invoices --< LineItems >-- Products
where the symbol denotes the cardinality: < is one-to-many, > is many-to-one, and many-to-many is implemented as two (or more) one-to-many relationships: a --< b >-- c.
So this translates into: a client can have many invoices, and each invoice belongs to one client; an invoice can have many line items, a product can appear in many line items, and each line items belongs to exactly one invoice and one product.
The tables have fields like
Clients: _pk_personID, name(s) etc.
Invoices: _pk_invoiceID, _fk_clientID, date etc.
LineItems: _pk_lineItem, _fk_invoiceID, _fk_productID, quantity etc.
Products/Catalog: _pk_productID, name (stockLevel, type etc.)
Note how each table has at least a primary key, plus one or more foreign keys, depending on their relationship to one or more other tables.
So a batch would be an Invoice (you could of course call it a batch); a receipt can be simply implemented by creating a layout based on LineItems, in which you show all related line items of a batch.
You can do this via a find, but since a batch 'knows' its line items via the relationship, you can use the Go to Related Record script step to 'find' those line items. Now sort those line items in any order you like before printing them.