11 Replies Latest reply on Nov 1, 2010 5:23 PM by WinstonChurchill






      I thought I'd begun to understand things a little better, with a lot of help from here I've managed to start working out most of my own scripts and functions now, however this one seems like it should be easy but has me stumped.

      I'm after using a field (say field "list") that will show collective data from (say field "name") as a list, for all records where field "A" and field "B" match the current record.

      I've tried numerous approaches (field calculation, script on record load, global and non global fields) and whilst I can find/sort records that match the criteria, only the "name" of the current record is ever placed in field "list" and not the other record "names" where fields A & B match.

      All fields are in the same table, fields A & B are not on current layout.

        • 1. Re: Lists

          If you have this relationship:

          TableA::PrimaryKey = TableB::ForeignKey

          This calculation defined in/from TableA: List ( TableB::DataField )

          will return a list of related values from TableB. Each value will be separated by a return character. That places each value on a separate line. If you don't want that format, you can use the substitute function to change returns into other characters such as a comma and space.

          • 2. Re: Lists

            I may be misunderstanding you, but does that not simply result in a list of values from one field.

            To put it in a real world situation the table I'm working with has records about envelopes (walls, ceilings etc). Records are kept in this table (envelope_records) for all envelopes from all zones (rooms) in a property. (The database will be used as a template, so each property will get it's own copy of the template - a new blank file for each property so to speak, so zone names etc won't be repeated by using the same database for another property)

            Whilst viewing data about a single envelope, I would like to see a list of other envelope records by their names (envelope_records::envelope name) which are in the same zone (envelope_records::zone_name) AND have the same orientation (envelope_records::orientation).

            Not sure whether it helps (or hinders) but the table (envelope_records) does have a parent table (zone_records) which uses (zone_records::zoneID) and (envelope_records::zoneID) to create the relationship, where although (envelope_records::zoneID) and (envelope_records::envelope_name) have different information in them they mirror each other (ie records with matching (envelope_records::zoneID)'s also have matching (envelope_records::envelope_name)'s

            • 3. Re: Lists

              I assumed the contents of a single field were what you wanted in your list.

              Why not use a portal to the related records for this? That would seem made to order unless there is some detail that I am missing?

              • 4. Re: Lists

                Sorry, somehow I have the feeling I'm missing the obvious here and looking a little dense, but the field that holds the names I want listed is in the same table used by the layout.

                • 5. Re: Lists

                  Can you given an example? I may not be understanding you correctly.

                  That said, it is possible to relate a table to itself so that you can put such a portal on your layout.

                  • 6. Re: Lists

                    I'll try to relate it to something simpler:

                    Table A = school classes (.....k10, k11, k12.....) Each class a separate record, Table B = children, each child a separate record.

                    In Layout 1 (table A form view) I enter general data about each class (year tutor name, what day they do sports etc), and via a portal on each record I create a record for each child in that class in Table B, I then go to Layout 2 (table B form view) and enter details about each child (male/female, number of detentions etc)

                    Whilst entering details about Bob Hope, who is from k10 and has had five detentions, I'd like to see a list on the same layout that shows just the names of other children, who are also from k10, are male and have had five detentions.

                    • 7. Re: Lists

                      I didn't relise I could relate a table to itself, I'll investigate.....

                      • 8. Re: Lists

                        That sounds like something you would set up with either Layout 2 or a customized layout that refers to the same Table B. You'd simply perform a find on this table specifying, K10, Male, and the "number of detentions" to get your "list".

                        • 9. Re: Lists

                          I've tried finds, but I only ever get it to return the name of the child whose record, I am currently working on. I've put pauses in on my scripts to verify the search works properly, they only ever seem to grab the info from the current info though.

                          • 10. Re: Lists

                            Isn't the current info what you want here? How are you specifying the criteria?

                            With my last post, I assumed you would have different criteria to match each time. If, instead you want "all student records with the same gender, grade level, and "number of detentions" as the current record and this is the same three values in every case, you could put a portal to a second table occurrence of students using a relationship that matches on these three fields.

                            • 11. Re: Lists

                              Simple as not realising a table can relate to itself, sorry about all the time it's taken but many thanks just the same.