Create auto-numbering field

[benwiggy]

I have a database which stores records, each of which has a "Type", as defined by a field with a value list.

I want to create a unique reference number for each record based on its SORTED position, in conjunction with the type of field.

In other words, the records are sorted by Type, then alphabetically within each type, and I want to have a field on the report which shows each record's numerical order. And yes, if I add a new record, then they all get a new number allocated to them.

Here's the catch: What might be really nice is for each type's number to start at a round number, thus: 1-99 for the first type; 100-199 for the next type, and so on.

(The data will not expand massively, so there's no danger of things bumping into each later in life.)

[comment]

Do you mean something like this?

RestartCount+.zip

[benwiggy]

Wow. Many thanks for your time and effort. I think this does what I want.

So the order count of each Type is done as a Summary: that seems simple enough. and then you do a calculation.

items = ValueListItems ( Get (FileName) ; "Categories" )

So this creates a value list of the Category types? (Any reason why I couldn't call on the Value List, if I have one already?)

sCount + 100 * ( ValueCount ( Left ( items ; Position ( ¶ & items & ¶ ; ¶ & Category & ¶ ; 1 ; 1 ) ) ) - 1 )

And this adds a number to the original count, which is a multiple of a hundred. I'm a bit lost at the innermost bracket, though. Any chance you could explain what's going on there?

Many thanks again.

[comment]

items = ValueListItems ( Get (FileName) ; "Categories" )

So this creates a value list of the Category types?)

No, this lists the items of an existing value list.

I'm a bit lost at the innermost bracket, though.

It calculates the ordinal number of the category in the value list.

[benwiggy]

It calculates the ordinal number of the category in the value list.

Yes, but I'm having trouble parsing it. Particularly the paragraphs.

[benwiggy]

OK, well never mind about trying to understand it.

However, if I resort the data, the number count seems to change.

What I what is a sorted number count that stays the same however the data is sorted, but changes if more data is added.

[comment]

What I what is a sorted number count that stays the same however the data is sorted

Well, that's somewhat different from:

I want to create a unique reference number for each record based on its SORTED position

See if this helps:

OrdinalInGroup.zip

[benwiggy]

Well, that's somewhat different

See if this helps:

Apologies. I should have been clearer that I wanted a numerical index based on A sort, but that stays that way regardless to actual sorting.

I'll look through your sample. Many thanks once again.

[benwiggy]

Hmmm.

This isn't quite working in my db.

The sort is by three fields: Type, Title, Subtitle. And I need a number that gives the count when sorted in this way.

I was a bit flummoxed by the 2nd instance of the same table in your example, but I can't see how to replicate this succesfully in my db. Attempts just seem to give a ? or just occasional "3".

Could we just try to get a continuous running count according to the sort, forgetting about separating the types into groups of 100s? Surely this should be easy?

[comment]

There is only one table - it has two occurrences on the relationship graph.

[benwiggy]

There is only one table - it has two occurrences on the relationship graph.

Yes, I understand that. But I don't understand what that does.

I've tried to recreate a similar setup in my database, creating another instance ("occurrence") of the table in the relationship graph, and relating the Type, Title and Subtitle fields to themselves ( ). Not clear with three fields what the operators should be.

However, I now have a count but it keeps resetting itself, either by Title or Subtitle, regardless of which of the three fields I Count.

Sorry if I'm being dense, but a bit of explanation of what these things do might help me apply the correct approach within my own db.

Is it a problem that the records in each of the sort fields are not unique, though no record has all three fields the same?

Just to reiterate, "Type" is a Value List, which has its own order, and Title and Subtitle are alphabetic.

Thanks

[kissbalazs]

is it possible that the "sorted by" element of "count of" does not work with a field whose type is not text but calculation?

[comment]

is it possible that the "sorted by" element of "count of" does not work with a field whose type is not text but calculation?

No.

Edited September 21, 2011 by comment

[benwiggy]

No.

To which question does this refer?

I have gathered that a self-join relationship produces some sort of relationship between records of the same fields (or something. Haven't entirely got my head around this.) So we're producing a subset of records that are less than or equal to the current record.... in some regard.

Just trying with only two sorting fields for now, and trying to copy the example database above as much as possible, I still get the count resetting.

Counting fields together (delineated by semicolons) doesn't seem to help either.

Would it be simpler to create a calculation field that is the merged text of all three fields, and then just count that? I guess that still leaves the "fixed sorting" problem, though.

[comment]

Just trying with only two sorting fields for now

I'm sorry - I didn't see your earlier reply. Your original question had the records sorted by one field. Now you have two or three. I am not sure if that makes a difference. Let me try and explain how this works. The self-join relationship:

Table::Category = Table 2::Category

AND

Table::SerialID ≥ Table 2::SerialID

makes every record that :

[a] is in the same category as the current record, and

was created before the current record (or is the current record)

related to the current record.

Therefore counting the related records returns the ordinal number of the current record in its category - with the numbering following the order of creation.

[benwiggy]

1. Aren't "Category" and "SerialID" two sorting fields?

2. Is the order of creation only significant because you are using the SerialID, which is auto-generated at creation? Or if I used a text field, then would the rationale be alphabetic, rather than "before in time"?

(Because if I add new records, I want them to slot into their correct order.)

3. If I were to add another sort field, would I just add it to the self-join table, and if so what operator would I use?

4. Would a running count be easier, i.e. the ordinal number of the record, without restarting at each category? In other words, just a record of the sort order, even when not sorted like that.

[comment]

Aren't "Category" and "SerialID" two sorting fields?

No. Category is a grouping field. SerialID is sort of a "sorting" field, in the sense that the numbering within a group follows the same order.

Is the order of creation only significant because you are using the SerialID, which is auto-generated at creation?

Yes.

Because if I add new records, I want them to slot into their correct order.

If you know the "correct" order, then why not number the records manually?

Would a running count be easier, i.e. the ordinal number of the record, without restarting at each category? In other words, just a record of the sort order, even when not sorted like that.

A running count, with or without restarting, will follow the current sort order - and include only members of the current found set. A relationship, OTOH, will ignore the current found set and its sort order.

[benwiggy]

If you know the "correct" order, then why not number the records manually?

Because if I have to add a record whose place is not at the end, I would then have to manually alter every record's number thereafter.

I will persevere with the self-join relationship, though from what you say, it sounds as if my suspicion is correct -- that because the "Title" and "Subtitle" fields are not individually unique, I may not have much luck.

Thanks

[comment]

from what you say, it sounds as if my suspicion is correct -- that because the "Title" and "Subtitle" fields are not individually unique, I may not have much luck.

Nothing from what I said can be used to confirm such suspicion - if only because I don't understand what you mean by "the "Title" and "Subtitle" fields are not individually unique". Do you want your numbering to restart on Title or on Subtitle? If the latter, are subtitles unique across the solution, or only within their parent title?

if I have to add a record whose place is not at the end, I would then have to manually alter every record's number thereafter.

I guess I am missing something here. How exactly do you want the records to be sorted, if only some of them have a value in the field used for the sort order?

[benwiggy]

OK. Here's the full spec.

The database is a catalogue of music manuscripts by a composer. The goal is to produce a complete list of works sorted by Type, then by alphabetically by Title. Each work should have a catalogue number, which is unique and increments according to the sort.

(Compare http://en.wikipedia...._Sebastian_Bach)

There may be more than one piece with the same name, e.g. "Gloria". However, the subtitle confers a description, such as "No.1 in G minor". Similarly, Subtitle records may not be unique, but the pairing of Title and Subtitle ARE unique. (Examples: "Kyrie No.1 in G", "Gloria No.1 in G", "Gloria No.2 in G".)

If I discover a new work, then it needs to go in its appropriate place in the list.

I thought that by starting each Type from a "round" number, e.g. 50, 100 -- then that would not disturb the order as much, and also give some clue about what Type the work is. ("If it's early 100s, it's an orchestral work.") So only the Type changing causes the number to "jump".

However, as this task seems to be much more complicated than I had originally imagined, I'll settle for a "straight" continuous count.

So far, my efforts have produced numbers for some records, but not all, and not unique numbers. It was that which made me think having non-uniques may be a problem.

You've lost me with your last question: The sort order is determined by the Type, then alphabetically Title then Subtitle. If I were to manually enter the catalogue number, as you suggested, then when I add a record that needs to go between records 4 and 5, I would have to change 5 into 6, 6 into 7... and so on. And the whole point of computers is to let them do the repetitive tasks.

This thread seems to have got quite convoluted. I was expecting that we could discuss a bit of formulae in an abstract fashion, without getting bogged down in the minutiae of my particular database. I prefer to learn generalities that can be applied to particulars. Teach a man to fish, and all that. Perhaps I should have outlined the whole thing at the start.

Many thanks for sticking with me!

[LaRetta]

I was expecting that we could discuss a bit of formulae in an abstract fashion, without getting bogged down in the minutiae of my particular database.

LOL, most complain that we are too general. People come here to get specific questions answered on their particular databases. You started out with, "I have a database which stores numbers ... I want to create a unique number ... " What else is Comment supposed to think you want?

It is easy to get frustrated when discussing complex issues but accusing the person assisting you (who is a top developer in this business) of being too specific is, well, silly. 'Specific' is what we do and 'specific' is what is required to pin down your requirements. You could not solve your request by learning a few 'generalities' and be 'taught to fish'; this is too complex an issue for you to be shown a few calc principles and shown how to hook two tables together and you go off and create it yourself in a few days; it just wouldn't happen.

Comment has given you a lot of his time and expertise. To call anything associated with him 'convoluted' is well, convoluted. You don't see others jumping in saying, "Oh, I see and Comment doesn't so let me help you." No, you don't because we read along also and can't figure out what you mean any more than Comment does. I question whether you are truly clear on the requirements. Every time I get into one of these places, it is because that is the case. Hang in there ... this isn't an easy business and written words are a difficult method of communicating. :smile:

[comment]

Is it possible for the Subtitle field to be empty?

[comment]

Hm. I read the "convoluted" part as a kind of self-flagellation...

[LaRetta]

:jester: ah. I should have known that Mr. Concise would never be called Mr. Convoluted, lol.

[comment]

Is it possible for the Subtitle field to be empty?

Actually, it doesn't matter. If (!) I am reading this correctly, you need to define a calculation field cFullTitle (result is Text) =

List ( Title ; Subtitle )

Then change the self-join to:

Table::Type = Table 2::Type

AND

Table::cFullTitle ≥ Table 2::cFullTitle

The rest of the fish should follow.

[benwiggy]

Actually, it doesn't matter. If (!) I am reading this correctly, you need to define a calculation field cFullTitle (result is Text) =

List ( Title ; Subtitle )

Then change the self-join to:

Table::Type = Table 2::Type

AND

Table::cFullTitle ≥ Table 2::cFullTitle

The rest of the fish should follow.

Yes, some Subtitle fields are empty. I shall try this and let you know how I get on. Once again, many thanks for all your help on this.

You started out with, "I have a database which stores numbers ... I want to create a unique number ... " What else is Comment supposed to think you want?

I don't understand the relevance of your question. Comment has supplied excellent help. If you read the first three sentences of my very post, you will see that I described what I want accurately, but in an admittedly ambiguous and non-optimal way. I said nothing about a database which stores numbers. Comment's first solution was useful, but I apologised for not being specific enough and leaving out once crucial fact.

accusing the person assisting you (who is a top developer in this business) of being too specific is, well, silly. 'Specific' is what we do and 'specific' is what is required to pin down your requirements. You could not solve your request by learning a few 'generalities' and be 'taught to fish'; this is too complex an issue for you to be shown a few calc principles and shown how to hook two tables together and you go off and create it yourself in a few days; it just wouldn't happen.

I did not mean to accuse anyone, let alone Comment, of the heinous crime of being specific. I was merely saying, in passing, that I prefer to learn generalised techniques, which enable me to apply them to a multitude of situations. It was an explanation of why I had not been more detailed in my first post.

This is the way I have learned throughout my entire life. But apparently I could never hope to accrue knowledge about general techniques in Filemaker and apply them to my specific problems. I'll bow to your experience of what you know of me, and accept that.

Comment has given you a lot of his time and expertise. To call anything associated with him 'convoluted' is well, convoluted.

I am extremely grateful for Comment's help and experience. I am not suggesting that his solution is sub-optimal (I'm sure it is highly efficient), but rather I was commenting that Filemaker's method for achieving this is more complex than I had anticipated.

I'm sure that if Comment feels slighted by my posts, he would say it himself.

Please let's not fight. If you have any input to the actual Filemaker problem, I look forward to reading it.

All the best

[benwiggy]

Actually, it doesn't matter. If (!) I am reading this correctly, you need to define a calculation field cFullTitle (result is Text) =

List ( Title ; Subtitle )

Then change the self-join to:

Table::Type = Table 2::Type

AND

Table::cFullTitle ≥ Table 2::cFullTitle

The rest of the fish should follow.

YES! This does now work.

Creating a new field that Lists both of the sort fields is the obvious, yet overlooked, step. I did have to Substitute the carriage returns out of the List in the calculation field, otherwise the results were a little off.

"Bumping" the starting number for each group to a round multiple also works, as per much earlier in the thread.

Many thanks for all your help. It has been invaluable.