Its like if i'm giving an ID to a field like 1,2,3,...
and relating a sub field to have values like field ID-subfieldID ie, 1-01,1-02,1-03
and 2-01,2-02,2-03, 3-01,3-02,3-03.......
I'm using Filemaker pro 12 trial version
Yes, but how is your table defined? are the "sub fields" records in a related table?
I get the series system that you want, but what does this numbering represent?
And while such an ID numbering system is sometimes needed for uses mostly external to the database, they really aren't good values to use as match field values in relationships.
A much more complete picture is needed in order to answer your question.
See Item 6.1: Forum Hints: Why didn't anyone respond to my question?
for example if a database is used for an organisation creating a id for departments which is 1,2,3,... and for creating divisions id which is a sub field in the department as 01,02,03...which will result as 1-01,1-02,1-03...2-01,2-02,-03....
And such should NOT be used in match fields to link records in a relational database, but if you need it for other purposes, such can be produced using relationships.
See if this demo file helps: https://www.dropbox.com/s/ggjy88cgkescopy/CategorySerialNos.fp7
Since you are using FileMaker 12, launch the application and use Open from the File menu to produce a copy of this file converted to the .fmp12 format.
my database has to look like this ..when i add new sub sector the value should increment by 1..
It seems very difficult to get enough detail with which to provide you a useful answer.
If this were my database, I'd use start with these tables and relationships:
Sectors::__pkSectorID = SubSectors::_fkSectorID
and it is possible that I might use one Data Source table with two Tutorial: What are Table Occurrences? for the same data source table for this if SubSectors can in turn have subSubSectors...
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Then the layout you show would be baed on SubSectors, the top left field might be _fkSectorID, the field next to it would be a name field from Sectors and the remaining fields would be from SubSectors.
And either a script or an auto-enter calculation would assign the compound serial number value when entering a value in the Sub Sector name field.
I can work from this starting point to explain that script or calculation in more detail, but does what I have just posted match what you have in mind?
I've created the tables and the relationship based on the id but i could not increment the sub sector id value using scripts..i now want to know how to increase the value of the subsector
I get that, but need to know if I am guessing correctly as to what you have set up in terms of tables, relationships and your layout. Once I have confirmation that I'm not working from a completely different design concept, I can proceed with a more detailed answer.
I've created two tables sector and sub sector and created a relationship between the two using the sector id and created two layout for both tables ...
Then in your screen shot, the layout should refer to Industry Subsector in Layout Setup | Show Records From
Each new record on this layout should then be a new record in Industry SubSector and selecting an Industry Sector should trigger a script that assigns the next value in the series for the group of Subsector records that are all linked to the same sector.
The Subsector ID field will use an auto-enter calculation to combine the Sector ID number with the next sequence number in the sequence of values for Subsector records linked to that Sector record.
Define a number field, Sequence, in Industry subsector.
Define a field of type calculation in km_Industry_Sectors, cMaxSeq, select Number as the result type and give it this expression:
Max ( Industry subsector::Sequence )
Define your Subsector ID field as a text field with this auto-entered calculation: Industry sector ID & " - " & Right ( "0" & Sequence ; 2 )
On the validation tab, select the Unique Values option to trap for cases where you might otherwise get duplicate values in this field.
Format the Industry subsector::Industry Sector Id field on your layout with a drop down list of Sector Ids from km_Industry_Sectors. Add the Industry Sector Name field from this table to your layout to be the field to the immediate right of the ID field.
Set up a script trigger on this ID field to perform this script. If you use a drop down list, use OnObjectSave, if you use a pop up menu, use OnObjectModify.
Set Field [Industry subsector::Sequence ; km_Industry_Sectors::cMaxSeq + 1 ]
the Sub sector id is increasing on a regular basis like 1-01,2-02,1-03.....but i want to filter the sector id and corresponding to each sector id my sub sector id has to increase like 1-01,1-02,1-03,2-01,2-01,2-03,3-01,3-02,3-03,,...
i have to get like this