1 2 Previous Next 15 Replies Latest reply on Nov 7, 2013 11:41 PM by BrianWyszynski

    One to Many tables? What am I missing

    BrianWyszynski

      Title

      One to Many tables? What am I missing

      Post

           Ok,

           Im trying to create a database with several nested one to many relationships.  Before my Relationship Chart starts looking

           like a third-world power grid, heres my question.  Because I cannot connect a table to more than one relationship, I have duplicated the tables in the relationship matrix.

           Now my relationships and pull downs dont work on the end form: Work Record [Portal: Items]... They work on the Work Request, the Equipment Add Form, and the main Work Record Form, but the Portal is not working.

           Why?!

           Images Attached.

            

            

      targetrel.jpg

        • 1. Re: One to Many tables? What am I missing
          BrianWyszynski

               Heres the FMP implementation

          • 2. Re: One to Many tables? What am I missing
            philmodjunk

                 When you say that you "duplicated a table", did you actually use the duplicate button in Manage | Database | Relationships to duplicate a Tutorial: What are Table Occurrences??

                 And your value lists that no longer work, how exactly do they fail to work? Are any of them conditional value lists?

            • 3. Re: One to Many tables? What am I missing
              BrianWyszynski

                   Phil,

                   Yes... I used the ++ button to duplicate.  All user tables are sourced from Users and all Equip Tables are sourced from Equip.  In the portal if I select the field to show WRItems:: UserID1 or UserID2, with drop down from value list Users (related), no list appears.  If I change each one to WRUsers1::User1 and WRUsers2::User2, the dropdowns work, but I get a No Relationship error.  If I have one User Table to Multiple WR Items Tables and each field on the portal is set to WRItemsU1 and WRItemsU2 respectively, it does the same thing.  When the pull downs work, the data wont validate against the Value List even if its right.

                   The portal form and WR Items Table has Eqp set to validate to member of Eqp Value List and validate User 1 and User 2 to Members of User Value List.

                   Im sure this is something super simple Im doing wrong...  what am I missing.

                   Thanks

                    

              • 4. Re: One to Many tables? What am I missing
                philmodjunk

                     On what table occurrence is your portal based? (what name is selected from Show Related Records From in Portal Setup...?) WR Items?

                     Is your value list a conditional value list? Does it specify the "include only related values..." option?

                     

                            If I change each one to WRUsers1::User1 and WRUsers2::User2, the dropdowns work, but I get a No Relationship error.

                     Are you changing the field formatted with the drop down list or are you redefining the fields specified inside Manage | Value Lists?

                • 5. Re: One to Many tables? What am I missing
                  BrianWyszynski

                       Yes, the portal is WRItems.  And yes, all Value Lists are related value lists.

                       I was under the impression that a portal had to have all the fields from the portal table. I havent modified the value lists since I built them because they work in the first instance of drop down on another form (no portal), like Equipment Add.

                       Using them in the portal appears to be too many steps removed from the main lists... or Im just being dense.

                        

                        

                        

                  • 6. Re: One to Many tables? What am I missing
                    philmodjunk
                         

                              was under the impression that a portal had to have all the fields from the portal table.

                         This is not the case.

                         The problem with your conditional value lists (are you sure that they need to be conditional value lists?) is that each requires that you specify a table occurrence as the source of the values (drop downs at top of dialog) and then you select a "starting from" table occurrence at the bottom of the dialog. The relationship between those two table occurrences as evaluated from the context of your current record on your current layout's table occurrence determines what values, if any appear in the value list.

                         So first, why does the value list need to be conditional? How do you need your relationship to limit the values in the value list?

                         (if we can eliminate the "include..." option your value list will not be empty no matter where you use it in your system.)

                    • 7. Re: One to Many tables? What am I missing
                      BrianWyszynski

                           interesting point.   Mentally I thought by stating that starting with table Clients... it would list Users from Client.  Since I have two fields, User 1 and User 2 and only User 1 requires data, I thought it would list users from the Client in the Master Form. 

                           So how do I list users and equipment from Client (Master Form) in Portal?  What tables am I supposed to build or do I build a second value list just for the portal based on the replicated relationship table?

                            

                      • 8. Re: One to Many tables? What am I missing
                        philmodjunk

                             Ok, that's the missing piece that I was looking for. You want the Client to Users relationship to limit what users are listed in the value list.

                             As I recall. This will work for the layout's record, but not as a means of adding a new portal record by selecting a value in the bottom "add" row of a portal.

                             Try this test and see if I have it correct:

                             Create a new record in the portal by entering data into one of the other fields in the portal row. Then click the layout background outside of the portal to commit the records. Now click on your value list formatted field and see if you get the expected list of values.

                             IF that works, there's a scripted work around that will avoid defining multiple value lists that are all the same except for the table occurrences referenced.

                        • 9. Re: One to Many tables? What am I missing
                          BrianWyszynski

                               OK... so If I were to add a button to the main form [Add Item] and use it to open a mini-window and copy the client code to that form [based on WR Items] do you think the Users and Equip Pull down will work. I could then copy the selected values back into a portal record and not worrry about the auto pull down on the portal.

                                

                                

                          • 10. Re: One to Many tables? What am I missing
                            philmodjunk

                                 Did my suggested test work?

                                 If so, removing "allow creation" as a relationship option and using a button next to the portal for adding new portal records should be a fairly simple "fix" to get what you need.

                            • 11. Re: One to Many tables? What am I missing
                              BrianWyszynski

                                   Ugh!!! I removed the "required value from equip field and from user" and then did what you said.  Why does that work???

                                   So I cant require the values at time of entry?

                                    

                                    

                              • 12. Re: One to Many tables? What am I missing
                                philmodjunk
                                     

                                          Why does that work???

                                     It's a conditional value list based on a valid relationship between the portal's record, back up through the chain of related records to the users table. At the instance you try to deploy the value list and select a user in the "add" row, you do not yet have a new record in the portal's table and thus the "chain" is broken at the very first link.

                                     Clicking an "add row" record is a very common method for adding a new portal row.

                                     

                                          I removed the "required value"....

                                     You might be able to add that back if you use the following simple script to create the new record:

                                     Freeze window
                                     Set Variable [$ID ; Value: Work Record::WR ID ]
                                     Go to Layout ["WR Items" (WR Items) ]
                                     New Record/Request
                                     Set Field [ WR Items::WR ID ; $ID ]
                                     Set Error Capture [on]
                                     Commit records/requests [Skip data entry validation]
                                     Go to Layout [Original Layout]

                                • 13. Re: One to Many tables? What am I missing
                                  BrianWyszynski

                                       Programatically, I cant understand why a new Portal record in a master record doesnt have an automatic

                                       relationshp with the master record and the related data tablies or value lists.  Seems almost silly.

                                        

                                       As for the button, am I supposed to put it in the portal next to the record, or outside the portal for it

                                       to work?

                                  • 14. Re: One to Many tables? What am I missing
                                    philmodjunk
                                         

                                              Programatically, I cant understand why a new Portal record in a master record doesnt have an automatic relationshp with the master record

                                         To repeat, at the time that you select from the drop down, there is no portal record. It does not yet exist and cannot be linked to the master record until you commit the record. The needed match field data does not get entered until the commit takes place.

                                          

                                         

                                              As for the button, am I supposed to put it in the portal next to the record, or outside the portal for it

                                         to work?

                                         Outside of the portal. Some of the starter solutions use this method so you may want to open and examine some of them for ideas.

                                    1 2 Previous Next