13 Replies Latest reply on Aug 16, 2012 12:31 PM by philmodjunk

    One to multiple records problem



      One to multiple records problem


      I have a table Model, with fields Factory, Model and Email


      Because One factory produces more than one model in most cases, I got multiple records with same factory code while differenct model number. And every factory has one email contact, my question is:

      How can i enter email address only one time for each factory, and I can demonsrate this email address associated with factory on table Model?


      Thank you!!

        • 1. Re: One to multiple records problem

          I think you need to go in and edit your post and re-post (maybe even a couple of times), to get it to appear correctly.

          • 2. Re: One to multiple records problem

            YOu should have a factory table. One record for each factory. A field in this table would record the email address.

            If you have a relationship such as:

            Factory::FactoryID = Model::FactoryID

            Then you can place Factory::EmailAddress on a layout based on Model and it will display the email address for the factory linked to that Model record.

            • 3. Re: One to multiple records problem

              Create a separate table for Factories, and put their contact details in there.  Then link the Models Table to it using a FactoryID.

              • 4. Re: One to multiple records problem

                The fields look like this:

                Factory     Model       Email

                A                 a1         a@gmail.com

                A                 a2         a@gmail.com

                A                 a3         a@gmail.com

                B                 b1         b@gmail.com

                B                 b2         b@gmail.com

                B                 b3         b@gmail.com


                Now I need to enter same email address under every model, to make Email box on Model layout demonstrate the factory email.

                I want to fill email address only once under every factory. How can i achieve this?

                • 5. Re: One to multiple records problem

                  I was thinking this way too. while I got dozens of factory by hand, and I do not have a file contains one record for each factory for me to import. Is there a method to generate this factory table from my original table? Or I need to enter factories one by one.

                  Now I got an add script on table Model, in furture, If I need to add new factories, how can i add to the factory table through this model table?

                  Thank you!!

                  • 6. Re: One to multiple records problem

                    You can define the table, Set a Unique Values, validate always falidation on your factory (name?) field. Then import from Models mapping the two factory fields. If you also define an auto-entered serial number field and enable auto-entry options during this import, you'll not only get one record for each unique factory name but generate serial ID numbers that will be a better choice for using in relationships than the factory name. (Like people, the name of a factory can change...)

                    • 7. Re: One to multiple records problem

                      Phil coule you please explain more about define the new table and set unique value? Sorry I did not get how to import or generate factories from my existing Model table. 

                      Do you mean add auto-entry data to get serial number in new Factory table?

                      Thank you!!!

                      • 8. Re: One to multiple records problem

                        Define a new Factory table. To do all that I have suggested requires at least three fields in this table: __pkFactoryID, FactoryName, Email

                        Define __pkFactoryID as an auto-entered serial number field.

                        Set factoryName to be Unique values, validate always. these are options on the validation tab in field options.

                        Go to a layout based on your new table.

                        Select Import Records | File from the file menu

                        Select the same file you are already working with as the file from which to import data.

                        Select the model table as the source table.

                        In the field mapping dialog, match up FactoryName in the Factory Table to the field in Model that stores the factory name.

                        A small dialog box pops up just before you start the import that asks if you want to enable auto-entry options. Select this check box.

                        When you import the data, the Unique values, validate always settings will prevent the import of duplicate factory names and this will thus load your table with one record for each factory named in your model table.

                        The auto-entry option will ensure that __pkFactoryID will enter a serial number for each new factory record.

                        At this point you can then start adding fields and building relationships to link the Factory table to other tables by __pkFactoryID. This will typically require linking a table to Factory first by Factory Name, then you add an _fkFactoryId field to the linked table and use Replace Field Contents to copy the ID from the matching factory record. Then you redefine the relationship to match by factory ID and you can then replace each use of the factory name in that table with a reference to the name field in the Factory table and then you can remove the factory name field from the related table.

                        • 9. Re: One to multiple records problem

                          Thank you so much for your thorough explain Phil Laughing!!!!

                          • 10. Re: One to multiple records problem

                            Seems I did not get the adding more factory part.

                            Now I add factory or model in Model table, and I have created a Factory table with factoryId number. While in the original table, there's no factoryID number recorded.

                            And If I want to add factory to Factory table through entering from Model table, how can I do that?


                            Thank you!!

                            • 11. Re: One to multiple records problem

                              You'll need to copy over the FactoryID into a factoryID field you add to Model.

                              Define this relationship to start:

                              Factories::FactoryName = Model::FactoryName

                              Add a FactoryID field to Model and place it on a Model layout. Then do this:

                              Show All records

                              Put the cursor in the FactoryId field and select Replace field contents

                              Select the calculation option and use this calculation:


                              To copy over the matching FactoryId number.

                              Then you can change your relationship to be:

                              Factories::__pkFactoryID = Model::_fkFactoryID

                              • 12. Re: One to multiple records problem

                                Thank you Phil!!

                                I have a follow up question, 

                                What if I want to add a new model, and the facotry is already on the list? If I use New/Request script, this will add a record for both sides, and in Facotry table, Id field will generate a new id for this record.

                                How can I avoid getting more than one id under one facotry?

                                • 13. Re: One to multiple records problem

                                  New Record/Request only creates a new record in the current table.

                                  If you choose new record while on your model table, you get a new model record, but no change occurs in Factories. You can then use a value list format on the _fkFactoryID field to select a factory for the new model record.