1 2 Previous Next 15 Replies Latest reply on Nov 27, 2013 8:37 AM by philmodjunk

    how to get values for sub fields

    Deshika

      Title

      how to get values for sub fields

      Post

           how to create sub fields like 1-01,1-02,2-01,2-03 in filemaker pro

        • 1. Re: how to get values for sub fields
          philmodjunk

               You'll need to explain what you mean by a "sub field" in much more detail.

          Please Help Us to Help You...

          • 2. Re: how to get values for sub fields
            Deshika

                 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.......

            • 3. Re: how to get values for sub fields
              Deshika

                   I'm using Filemaker pro 12 trial version

                    

              • 4. Re: how to get values for sub fields
                philmodjunk

                     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?

                • 5. Re: how to get values for sub fields
                  Deshika

                       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....

                  • 6. Re: how to get values for sub fields
                    philmodjunk

                         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.

                          

                    • 7. Re: how to get values for sub fields
                      Deshika

                           my database has to look like this ..when i add new sub sector the value should increment by 1.. 

                      • 8. Re: how to get values for sub fields
                        philmodjunk

                             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----<SubSectors

                             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?

                        • 9. Re: how to get values for sub fields
                          Deshika

                               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

                          • 10. Re: how to get values for sub fields
                            philmodjunk

                                 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.

                            • 11. Re: how to get values for sub fields
                              Deshika

                                   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 ...

                              • 12. Re: how to get values for sub fields
                                philmodjunk

                                     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.

                                     Commit Records
                                     Set Field [Industry subsector::Sequence ; km_Industry_Sectors::cMaxSeq + 1 ]

                                • 13. Re: how to get values for sub fields
                                  Deshika

                                       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,,...

                                  • 14. Re: how to get values for sub fields
                                    Deshika

                                         i have to get like this

                                    1 2 Previous Next