13 Replies Latest reply on Jan 3, 2014 12:11 PM by RishiTrikha

    inputting data problem

    RishiTrikha

      Title

      inputting data problem

      Post

           In my relational database, I have a key field which is an ID number. It is formatted as a text, because the ID number can start with a letter. For some records, when I try to input data, I cannot update/add data to a certain field. For the most part, this seems to be the case when the ID number starts with a letter. I can update/add data in this same field for another patient as when I click on a field it allows for data entry (the cursor shows up and a yellow-orange box surrounds the field, indicating that the field can be modified). This yellowish box does not appear in the same field but for another record.

        • 1. Re: inputting data problem
          philmodjunk

               Can't really answer without a more detailed understanding of your layout and the underlying tables and relationships. You may need to upload some screen shots of both the layout and your relationship graph.

               Explaining the function and purpose of this ID number field might also be helpful--especially why it has a letter as the first character.

          • 2. Re: inputting data problem
            RishiTrikha

                 Unfortunately I can't upload a screen shot due to privacy issues. This ID field is my key field that everything relates back to. I have multiple tables, all of which have the same ID field, which relates to one initial table. The relationship is ID field to ID field, such that there is a one-to-many relationship in regards to the first table. This ID field is an ID number (may start with a letter) unique to every record. There are over 300 records that have an ID that starts with a letter, so changing each record such that they are all just numbers is not a reasonable options. Please let me know if there is any other information I can provide that will help with troubleshooting this problem.

            • 3. Re: inputting data problem
              philmodjunk

                   I haven't suggested that you not use a letter. I've asked why it is necessary as part of understanding the purpose of your database. Should such a change be necessary, updating 300 records with a different value isn't really that difficult to do. Compared to some of my tables where the record count is in the millions, 300 records is a drop in the bucket. And the tools that modify the value in one table can also make matching changes in related tables. But please note that I have not suggested that this is necessary.

                   One way or another, you'll need to provide more info about the design of your database and the underlying relationships. Whether that means you use words or pictures is up to you.

                   Here are some questions to start the process:

                   I am assuming that you are describing issues with one specific layout so correct me if this is not the case.

                   Enter layout mode and click the id field on your layout. In the "Display data from" tab on the inspector's data tab, note the text to the left of the :: characters. Then look up in the tool bar where you see "Table:" just to the right of the layouts drop down. Do you see the same exact name there as this text?

                   This will tell me if you are entering data into a field from a related table or the layout's table.

                   Is this a field located in a portal?

                   Is the field formatted with a value list?

                   While in layout mode, right click the field and select script triggers. Do you see any script triggers selected for the field?

                   You've indicated that sometimes you can enter the data into this field and that sometimes you cannot. Look for any pattern to when you can do this and when you cannot. If you spot such a pattern, let us know what you have observed as this could be a very useful clue.

              • 4. Re: inputting data problem
                RishiTrikha

                     " Enter layout mode and click the id field on your layout. In the "Display data from" tab on the inspector's data tab, note the text to the left of the :: characters. Then look up in the tool bar where you see "Table:" just to the right of the layouts drop down. Do you see the same exact name there as this text? "        Yes, they are exactly the same.

                     This field is located in a portal. This field is not formatted in a value list.

                     No script triggers are selected.

                     It seems as though this problem mainly occurs when I create new records and for the most part doesn't occur with fields that are already defined. My database is formatted such that there are many tables relating back to one root table. I have imported all previous data from an excel sheet to the database and for this data, the problem I'm encountering above doesn't seem to be happening. Again it seems to be that only new records present this problem.

                     Hope this helps

                • 5. Re: inputting data problem
                  philmodjunk

                       This field is located in a portal. This field is not formatted in a value list.

                       This would seem to be a problem. If the field is located in a portal row they should NOT be exactly the same in most cases. What are the relationship details between the layout's table occurrence and the portal's. A "Table Occurrence" is the "box" with the same name found in Manage | Database | Relationships so you'll find a box with the same name as the "Table:" name I had you check. If you open portal setup for the portal and check "show related records from", you'll find the name of the table occurrence for the portal. How the two are connected, what check boxes are selected in the relationship details dialog (Double click the relationship line to open) all may have a bearing on this.

                       But at first guess, it's possible that your ID field should be specified to be from the portal's table occurrence instead of the layout's.

                  • 6. Re: inputting data problem
                    RishiTrikha

                         Ok got it. I have many different tables and the portal is used to show data from another table in a layout I have titled master layout (the very first layout). I have changed many different aspects of the field type using the inspector, however I don't think the problem lies within the field type as I do not have this problem with the same field in other records.

                    "it's possible that your ID field should be specified to be from the portal's table occurrence instead of the layout's."     How would I go about changing how my ID field is specified without losing any data or losing any power of my database?

                    • 7. Re: inputting data problem
                      philmodjunk

                           I can't answer that without an answer to the question I asked in my last post:

                           

                                What are the relationship details between the layout's table occurrence and the portal's?

                      • 8. Re: inputting data problem
                        RishiTrikha

                             sorry I don't quite understand what you're asking, I'm a beginner. I have set up the master layout such that there are many portals that all show the data from different tables. the tables that the portals show are all related to one root table through the key field (ID). the root table has a one to many relationship

                        • 9. Re: inputting data problem
                          philmodjunk

                               I gave you directions in how to answer the question in a previous post.

                               

                                    What are the relationship details between the layout's table occurrence and the portal's. A "Table Occurrence" is the "box" with the same name found in Manage | Database | Relationships so you'll find a box with the same name as the "Table:" name I had you check. If you open portal setup for the portal and check "show related records from", you'll find the name of the table occurrence for the portal. How the two are connected, what check boxes are selected in the relationship details dialog (Double click the relationship line to open) all may have a bearing on this.

                               What I am looking for are what fields are selected as match fields and what options are selected in the relationship details dialog for that relationship. In particular, is "allow creation of records via this relationship" selected?

                               From what you describe, I think that your relationship will match this pattern:

                               LayoutTableOccurrence::IDField = PortalTableOccurrence::IDField

                               And I would guess that "allow creation" is enabled for the portal's table occurrence in this relationship.

                               If so, then this should be all that you need do:

                               enter layout mode.

                               Select the ID field in the portal row and delete it. you don't need it in your portal at all.

                               If you were to edit this field--either the copy of this field in your portal's table or the copy of the field in your layout's table, it would "break" the relationship linking your portal's records to the current record in your layout.

                               If you do want to see the ID field in your portal--which will show exactly the same value in every row of the portal, then browse mode access should be denied in most cases to keep from accidentally modifying the record.

                               But please note that I am making a number of guesses about what you have set up that could be wrong.

                                

                          • 10. Re: inputting data problem
                            RishiTrikha

                                 OK great, thank you. I had misunderstood you before. All of my relationships did not have "Allow creation of records via relationship" checked. So will I have to go through each relationship and check this box for each?

                            • 11. Re: inputting data problem
                              philmodjunk

                                   It's an option you can select if you want. I expected that it was already enabled from what you have described previously when you try to enter data into this field. If the option is enabled for a table occurrence for a portal, you can create new related records in the portal's table just by entering data into a blank row of the portal. FileMaker will automatically copy over the Match field value(s) into the new record to link it to the current parent record--the current record on your layout where you placed the portal in most cases.

                              • 12. Re: inputting data problem
                                RishiTrikha

                                     I see. Is there any other way to ensure that all relationships have the "Allow creation of records in this table via this relationship" checked other than manually clicking each relationship line and check them individually? It looks like it will be difficult to click each individual line as they are very close together.

                                • 13. Re: inputting data problem
                                  RishiTrikha

                                       not to worry, I just did it manually and individually. Thank you for your help!