1 Reply Latest reply on Dec 9, 2013 5:56 AM by philmodjunk

    Find Year records in a related table



      Find Year records in a related table


           In table A, I have the field Year = Year(datefield). In related table B, I have a layout that includes the field. I need a script that finds records in table B for a specified year. When I run a find script step that inserts a value into the year field, it finds many different years.

        • 1. Re: Find Year records in a related table

               In layout setup, the Show Records From drop down specifies the table occurrence on which a layout is based. If your layout is based on Table B and you perform a find where criteria, such as the year is specified in fields from the related table A, you find all records in Table B that have at least one related record in Table A that match your criteria. In your case, that means all Table B records that have at least one record in Table A with the specified year.

               The script then returns you to browse mode where your existing relationship between tables A and B controls what Table A records appear on your Table B based layout--the find criteria you specify do not affect what related Table A records appear, only what Table B records are now in your found set and thus you see related Table A records (In a portal, I would guess) for all kinds of different years. (But there should always be at least one with the specified year.)

               To see only Table A records of a specified year:

               1) Do the search on a layout based on Table A instead of Table B.

               2) Use a portal filter to filter by a specified year.

               3) Add a match field to the relationship between Table's A and B that allows you to choose a year and then only see related records from A based on that year.