Find records and increment serial number

[welby]

HI,

I am at a complete loss about this.

Here's what I'd like to do:

My simple records:

record 1|xxxx

record 2|xxxx

record 3|xxxx

record 4|yyyy

record 5|yyyy

I would like to look for occurences in the 2nd field. eg xxxx and where this occurs I want to have the same serial number for that found set and then on the second set yyyy increment by and repeat all the way down, incrementing by 1 for each found set so it ends up like:

record 1|0001

record 2|0001

record 3|0001

record 4|0002

record 5|0002

etc etc.

It doesn't even have to be in the same field. The result could be placed in another field.

I'm stumped on this. Any help appreciated. Thank you.

[comment]

This is easy to do in versions later than 7, but rather difficult in version 7. See the attached.

SerialInCategory.fp7.zip

[welby]

Hi,

Thanks for the help. I will have a look at the example. Does the sample still appply or is there an easier way in version 9?

I have bitten the bullet and upgraded to 9. I will alter my profile.

Thanks again for your help in this matter.

EDIT: ok I can see where we are going on this. The example is great, but I would like the cSerialInGroup to have the same number if the Category is the same, and not a running count as in the example.

Thanks.

Edited November 14, 2008 by Guest

[comment]

MUCH easier:

EDIT:

The example is great, but I would like the cSerialInGroup to have the same number if the Category is the same, and not a running count as in the example.

Oh, I see - you want to number the groups. Hold on.

SerialInCategory8.fp7.zip

Edited November 14, 2008 by Guest

[welby]

Hi, thank you for the fast response.

That example shows a report with the serial number for each of the categorys.

I would like to see where for example the category is the same I want the serial number to show in the cSerialInGroup field as the same.

for example: Alpha = 1 Bravo = 2 and so on so each group shows a non unigue serial number.

I have attached an image to show the result that I require.

Thank you for your patience.

Edited November 14, 2008 by Guest

[comment]

OK, here's the same thing as before, adapted to increase at category break.

NOTES:

1. If you need this for a report, then you can use the interim field sRunningCount directly, and eliminate the last field.

2. There's probably a simpler way to do this - it depends on what you're actually doing. For example, the categories could be numbered in their own table, and you could fetch the number via a relationship from there. Or you could define a value list of categories, and calculate the position of the current category in that list.

SerialOfCategory.fp7.zip

[welby]

Hi,

that's exactly what I require.

I'm using filemaker as a filter for data conversion and this work's perfectly.

I don't need a report and export the data as needed.

a very BIG thank you!

edit:

You have a calculation in cInvCount field as: 1/GetSummary ( sCountRecords ; Category ) and the result is a number.

The Category field is text and not a number so how does this work?

edit 2:

decided to create a separate table and use a relationship - makes life simple - thanks again!

Edited November 14, 2008 by Guest

[comment]

The Category field is text and not a number so how does this work?

The count of records in a category is always a number - regardless of the type of the Category field. See also:

http://fmforums.com/forum/showpost.php?post/305964/