11 Replies Latest reply on Feb 9, 2017 12:41 AM by Dinesh Kumar

    FTS Indexing in Join Forms

    Dinesh Kumar
      Share This:

      Hi All,

       

      We are trying to enable FTS in our ARS 8.1 SP1 environment.

       

      In FTS logs we can see our custom join forms are showing as "Successfully indexed data for schema XXXX", But we have not enabled FTS or MFS in those forms.

       

      If we check for the FTS / MFS enabled fields(using DB queryList of FTS indexed fields? ) we are getting around 60 field's, But in the FTS startup logs its showing more than 1000 fields "Recognizing field 'InstanceId'(179) on form 'TMS:Task'(1329) as being selected for MFS Category field (InstanceId)".

       

      Why there is a difference in DB query and FTS logs?

      Is this the normal behavior of FTS for join forms? 

       

      Do we have any doc for how FTS is recognizing field's and how Join form fields scanned?

       

      Regards,

      Dinesh Kumar.

        • 1. Re: FTS Indexing in Join Forms
          LJ LongWing

          Dinesh,

          Unfurtunately, that SQL query is not 100% accurate for determining which fields will be fts indexed.  This one is more complete.

           

          select distinct a.name, f.fieldName

          from arschema a

            inner join field f on f.schemaId = a.schemaId

            left join field_char fc on fc.schemaid=a.schemaid and fc.fieldId = f.fieldId

            left join field_diary fd on fd.schemaid=a.schemaid and fd.fieldId = f.fieldId

            left join field_attach fa on fa.schemaId=a.schemaId and fa.fieldId = f.fieldId

          where

            (f.objprop like '%60055\4\%' or f.objprop like '%\60056%\2\1%' )

            or fc.fulltextoptions > 0

            or fd.fulltextoptions > 0

            or fa.fullTextOptions > 0

          order by 1,2 asc

          2 of 2 people found this helpful
          • 2. Re: FTS Indexing in Join Forms
            LJ LongWing

            Additionally, for 'join' forms, because joins are not managed in quite the same way as Regular forms, you can either index them or not, but I believe you need to execute some special run-processes to get it to re-index the join form because of lack of proper 'modified date' fields.

            • 3. Re: FTS Indexing in Join Forms
              Dinesh Kumar

              Hi  LJ,

               

              Thanks for sharing the query, I ran the query and got around 1100 fields. In the list, we have fields in Change form also, if I go to dev studio and check the same field for FTS option is still showing None. So where we can find that, this particular field is FTS/MFS enabled field?

               

              Is there any way we can exclude join from indexing?

               

              DB Results :

              Dev Studio :

               

               

              Regards,

              Dinesh Kumar.

              • 4. Re: FTS Indexing in Join Forms
                LJ LongWing

                Right at the bottom of your screenshot, being cut-off is the 'Full Text MFS Category Name'...if that value is set, then it's indexed for MFS regardless of if you have the 'Index for FTS' set to 'None' or not....remove that attribute and the field will be removed from the FTS Index

                2 of 2 people found this helpful
                • 5. Re: FTS Indexing in Join Forms
                  LJ LongWing

                  and FYI, the tool I mentioned in the other thread (FTS Index – A Programming Legacy) not only tells you what fields are indexed, tells you why they are indexed, and provides options to automatically remove indexes from forms/fields through it....if that's something you are interested in

                  2 of 2 people found this helpful
                  • 6. Re: FTS Indexing in Join Forms
                    Dinesh Kumar

                    Thanks for suggesting (FTS Index – A Programming Legacy   tool

                     

                    Is there any way to remove the Join form from FTS indexing?

                     

                    In our FT_pending table, we are seeing operationtype like -10 without any Fields ID and update time like 12/09/1971.

                    Both records refer's to join forms, Is there any reason for having -ve values in operationtype and without field id?

                     

                     

                    Regards,

                    Dinesh Kumar.

                    • 7. Re: FTS Indexing in Join Forms
                      LJ LongWing

                      I've never delved deep into the FT_Pending table, so I unfortunately don't have any of that insight...

                      • 8. Re: FTS Indexing in Join Forms
                        Dinesh Kumar

                        Do we have any document for how FTS indexing is done on Join forms?

                         

                        1. Does the Join form records are getting indexed or only base form records get indexed?

                        2. How do we come to know that particular field is indexed successfully or re-index required?

                        3. In which order the form records will come into ft pending table?

                         

                        Regards,

                        Dinesh Kumar.

                        • 9. Re: FTS Indexing in Join Forms

                          1. Does the Join form records are getting indexed or only base form records get indexed?- I doubt Join form are indexed separately. The Base form records are indexed.If the search is being executed against a join form the field(s) on the join that you intend to search upon should be FTS enabled.

                           

                          2. How do we come to know that particular field is indexed successfully or re-index required? At field level i doubt , at record level you can see in FTS logs. There is a tool from which you can read the FTS indexes. [ARS 8.1] How to browse your FTS indexes - YouTube

                          3. In which order the form records will come into ft pending table?-- BMC team may answer this it all internal mechanism.

                           

                          just to add.

                           

                          How to Check FTS Indexed Fields on DataBase- Example is for HPD:Help Desk Form.

                           

                          select a.name, B.fieldid, b.fulltextoptions from (field_char b join arschema a on a.schemaId=b.schemaId),field c  where

                          fullTextOptions>=1 and c.fieldId=b.fieldid and c.schemaId=b.schemaId and a.name='HPD:Help Desk'

                           

                          Regards

                          RP

                          • 10. Re: FTS Indexing in Join Forms
                            Dinesh Kumar

                            In the FTS logs it's showing Join form records also indexed successfully, Is there any way to exclude join form from FTS indexing.

                             

                            Regards,

                            Dinesh Kumar.

                            • 11. Re: FTS Indexing in Join Forms
                              Dinesh Kumar

                              Hi All,

                               

                              Join form records are also getting indexed, If you want to remove the join form records we need to remove it manually or by using FTS Index – A Programming Legacy  tool or using Trending in Support: FTS Fortification.

                               

                              Thank you LJ LongWing