6 Replies Latest reply on Apr 18, 2014 1:25 AM by kevork

    Keep creating new record in related table

    kevork

      Title

      Keep creating new record in related table

      Post

           I cannot understand where the problem lies. I have two tables, Member and Status, joined by a join table Status_Member. I want the status of each member to show in a pop-up list in the Member records. The problem is that every time I allocate a status to a member, a new record is created in the status table. The obvious solution seemed to be to remove "Allow creation of records etc" in the relation but if I do that, the pop-up stops working. And strange to say, if I conver the pop-up to a pull down, the status name ceases to be displayed and is replaced by the record number.

      Untitled.png

        • 1. Re: Keep creating new record in related table
          philmodjunk

               From your relationships, it looks like your popup menu refers to Status_Member::_kfStatus. That's a field in the Status_Member table so selecting a value in it will create a new record in Status_Member if a related record in Status_Member does not already exist. That would seem what should take place here given that design.

          • 2. Re: Keep creating new record in related table
            kevork

                 Thanks, that makes sense. But this is where I get confused. I want to have a picking list for membership status and have chosen to do it by setting up a related table. This (seems to have) worked for me before without this problem. 

                 I suppose I could have a custom value list instead but then I would not be able to produce reports that lists the different categories of membership. I have tried doing that and I cannot get a list for each status.

            • 3. Re: Keep creating new record in related table
              philmodjunk

                   You don't need a related table just to get a value list. Nor do you need a related table in order to set up a value list with the "use values from a field" option. The table that serves as the source of values does not need to be related to your layout's table at all in some cases.

                   From what you describe, you need to add a field for the status to your member table and format it with the value list instead of entering data into a field from a related record.

                   The combination of a status table, a status_member table and  member table is typical of a many to many relationship where you want to link one member record to many status records and one status record to many member tables.

              • 4. Re: Keep creating new record in related table
                kevork

                     Thanks, I understand. You prompted me to do a bit of learning by trial and error. I compared this database with other tables where I have had many-to-many relationships with success. I see the problem. In previous databases, I have related records to each other via portals. This does not create a new record. With the database we are discussing, I used a simple field and that does lead to creation of new records.

                What prompted me to try this out was that I want to have a separate table with status because (1) I want to create lists of members according to status and I cannot get that to happen with a custom value list (2) a member may change status and I want  to create a status history eventually. Can you point me in the right direction?

                • 5. Re: Keep creating new record in related table
                  philmodjunk
                       

                  (1) I want to create lists of members according to status and I cannot get that to happen with a custom value list

                  Such can be done from a custom value list. you can get such lists using a custom value list  or a table based list and this does not require a many to many relationship to do.

                       

                  (2) a member may change status and I want  to create a status history eventually. Can you point me in the right direction?

                       This also does not require a many to many relationship, just a one to many relationship from members to status history. You'll need either a portal or a script however if you want to record the status of each member at different points of time in their "history". That added table would only be needed to record current and previous status values. A Portal sorted to list the most recent status first would be the simplest. A script that takes the members current status and logs it in the status history table by creating a new record might also be used.

                  • 6. Re: Keep creating new record in related table
                    kevork

                          Thanks, I tried (1) & (2) and they worked.