7 Replies Latest reply on Mar 14, 2012 6:58 PM by JohnTracy

    Adding records to a linked table based on a conditional field of the present filtered set.

    JohnTracy

      Title

      Adding records to a linked table based on a conditional field of the present filtered set.

      Post

      Per post title, I have a need to have a drop-down object that contains records of a filtered table and related to the current open record.  What I have in mind for appearance and action, would be like a drop-down value list but ancually be selected fields in filtered records of a related table and anchored to the current record in List View.  I don't care if this drop down lays over subsequent records of the working view or <temporarily> pushes the subsequent records down.  I will need to add records to this drop-down portal (window) before committing the present open record.

      A conditionally created portal may work, but I can't find a way to do this.

      Alternatively, creating a <temporary> new window with a filtered layout and based on the related table would probably work.  Additionally this new window would idlealy be anchored to the initial window so it appears as a drop-down.  I haven't tried this yet, but I expect I could get it to initially appear in the right spot but I don't have high confidendce that I could get it to move with the window that created it.

      Does any one have any suggestions?  Please.

        • 1. Re: Adding records to a linked table based on a conditional field of the present filtered set.
          philmodjunk

          I think you want a conditional value list. It depends on what you mean by: "...that contains records of a filtered table and related to the current open record."

          A conditional value list cannot "contain records" but it can list a set of values from a field, such as the table's primary key, in that table filtered to list only those related to the layout's current record.

          If that's what you want, start with these links:

          Forum Tutorial: Custom Value List?

          Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

          Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

          A portal of related items where you click on to select it is also possible. Both that approach and a conditional value list require that you be able to set up a relationship that correctly matches to the desired records.

          • 2. Re: Adding records to a linked table based on a conditional field of the present filtered set.
            JohnTracy

            I have been trying several ways to accomplish my goal in the past few weeks.  I thought I had it using a portal.  (Well, not exactly what I want but an adequate solution.)  

            What I'm trying is to create a parrent-child relation between some of the records.  Since it would be helpful in the long run for all records to be in the same database, I found that I could have a portal of the same main data base on the same layout by using creating a relation through a "Table 2" of the same table.  This effort was moving along tickety-boo, but have now hit an issue that may not be easily solveable whith my present tack.  The main working layout is in List view and I switch to a different layout in Form view that selects the recordNumber of the parrent..  The appearance of the two layouts is similar and the change in layouts provides the necessaty white space to contain the portal.  All records have an automatic sequence field and when a new record is to be a "child" its parrentSequence field is populated with the sequence number of its parrent. The records in the List view show about 18 fields.  The records in the portal of the Form view only show about 5 fields.  The rest are set from the "Parrent" record.

            The problem I now have is that when I switch back to the List view (iow the children are no longe visible), the 5 fields of the last record in the portal field are copied to the parrent.  I can't find anything I have done to cause this and suspect it is one of the ways portals work.  If this is the way portals work, is there some way to stop this automatic copying <same named> fields from portal record to the selected record?

            • 3. Re: Adding records to a linked table based on a conditional field of the present filtered set.
              philmodjunk

              Automatic copying of data from the portal record to the parent record simply by changing layouts is not something that should take place. Why it appears to you that this is happening is not a question I can answer from what you have shared so far.

              What I can deduce from your posts is this:

              You have a self join relationship between two occurrences of the same table:

              table::SerialNumberField = table 2::ParentSerialNumberField

              YOu have a list view layout and a form view layout. As a guess, both layouts list "table" not "table 2" in the "Show records from" drop down found in layout set up and the portal on the form view layout shows "table 2" in the "Show Related Records From" drop down found in portal set up for the portal.

              Is this what you have set up?

              If so, check the fields in the form layout while in layout mode and check any that show ::Field name if located outside of the portal or that do not show ::FieldName and are located inside of the portal. The :: indicates that the field comes from a related table occurrence rather than the table occurrence identified in Layout Setup | Show Records From and some confusion here could result in editing fields from the parent record when it looks like you are editing fields from the child record.

              • 4. Re: Adding records to a linked table based on a conditional field of the present filtered set.
                JohnTracy

                Thanks.  You nailed it in your understanding of what I'm doing.  Your first statement: that it shouldn't be happening; made me look harder for what I did wrong.

                Here's what I found: I had assigned the wrong database table to the "table view" layout.  The data base I had assigned had only a fleeting relation to the data in the layout.  My bad.  It's what I get for giving two tables similar names.

                 Anyway, Things seem to be working as expected now.

                • 5. Re: Adding records to a linked table based on a conditional field of the present filtered set.
                  JohnTracy

                  I spoke too soon.  I've advanced the development, and the problem of backfeed from a portal has returned.

                  In layout edit mode, the fields have a green or yellow lollypop at the right edge.  I have noticed that the fields that are causing me trouble have yellow lollypops.  Other firlds also have yellow lollypops but these are usually calculation fields.  Anyway... what is the meaning of the colors?  If I could find a reference that explains these colors, it might help me debug.

                  • 6. Re: Adding records to a linked table based on a conditional field of the present filtered set.
                    philmodjunk

                    The colors predict how fast a search specifying criteria in that field will be performed. Green is faster than yellow.

                    Chances are that the fields with the yellow magnifying glasses are fields from a related table occurrence. (Unstored calculation fields will also have a yellow badge.)

                    Do you see field names that start with ::? That's the most obvious indicator that they are not specified as coming from the table occurrence specified in layout setup. In layout mode, you can also click the field and check the display data from box on the inspector's data tab or double click it to check the name in the drop down at the top fo the specify field dialog that appears.

                    • 7. Re: Adding records to a linked table based on a conditional field of the present filtered set.
                      JohnTracy

                      Eureka! I found the problem.

                       

                      I had noticed that after creating children to a parent record and selected the parent record the unique fields of the last child record would suddenly back-fill (over write) those fields in the parent when I tabbed into the first field that would change.  Well, it wasn't tabbing into the field that initiated the problem, it was the field I was tabbing out of.  I had attached an exit script to that field that would automatically fill in several other fields by finding the last match.  The script wasn't finished because I didn't have enough of the pieces done yet to put a condition on the auto pre-fill that it was only for new records.

                       

                      I hope you don't have to hear from me again on this issue.