1 2 Previous Next 15 Replies Latest reply on Jan 5, 2011 9:55 AM by philmodjunk

    Multiple due date fields



      Multiple due date fields


      I work in a University Publications Office and we use filemaker to generate or work orders and track the progress of our projects. I am redesigning our work order form, from scratch. We need to list at least three "proof due" date fields with corresponding "proof returned" date fields but I would like to have a way to additional proof due and returned dates as needed. What would be the best way to do this short of having 10 "proof due" fields that show up on each form.

        • 1. Re: Multiple due date fields

          Use a related table for recording your due dates. Then you can use a portal and add as many due dates as you need simply by adding more related records in your portal.

          • 2. Re: Multiple due date fields

            Thank you so much for the quick response. I am very new to file maker and I am not sure if I understand your answer. I am trying to keep each project 1 record in the file. I don't think I understand what a table is and how it relates to data in a field or a record. Searching "related table" in filemaker help didn't get me anything that I understood.

            • 3. Re: Multiple due date fields

              Look up "portal" in FileMaker help. This is an extremely useful feature of FileMaker and can do many things for you once you understand how to use it.

              In any given file, you can define any number of different tables. Go To Manage | Database | Tables and you'll see a section of FileMaker where you can create as many different tables as you need. Once you've created a table, you can click the Fields tab to define the fields that you need for any of the tables that you've created. Click the Relationships tab and you can then link your tables in relationships.

              1. Starting from the Tables tab, enter DueDates as a table name and click Create.
              2. Click the Fields tab and define at least these fields in your table. (You may want to come back here and add more fields later.)
                PublicationID (Number field)
                DateDue (Date field)
                Description (Text) // so you can document what this due date represents
              3. In the table drop down near the top of this section, select your original table. I'll call it Publications in this example. If you haven't already defined such a field, define a number field, PublicationID, click the Options button and set it to auto-enter a serial number.
              4. Now click the Relationships tab and you should see two "boxes" labeled Publications and DueDates. These boxes are called Table Occurrences (TO for short). Drag from PublicationID in one box to PublicationID in the other to link them in a relationship. That creates this relationship:

                Publications::PublicationID = DueDates::PublicationID

              5. Double click the line that now links these two table occurences to pop up a dialog box for specifying the details of your new relationship. Click the "Allow creation of records via this relationship" for DueDates, NOT for Publications. Also select the Delete option for DueDates and again DO NOT select this option for Publications.
              6. Now click OK to save all these new changes and return to your Publications layout. Enter layout mode and use the Portal Tool to "draw" a box on your layout for your portal. In the Portal setup... dialog select the DueDates table and then specify the options you want. Usually, you want a scroll bar and the ability to delete portal rows. You might also want to sort your duedate records by DateDue but you don't have to.
              7. When you click OK, you'll be asked to specify what fields to display in the portal. Select both the date and description fields. You can add the PublicationID field if you want to, but it's not needed in the portal.
              8. Now return to Browse Mode and try entering due dates and descriptions in your new portal.


              Note: your posts indicate that you are new to FileMaker and very likely new to the concept of Relational Database design. You would be well advised to invest some time in tutorials and/or a book or two on FileMaker to help get you started.

              • 4. Re: Multiple due date fields

                This was a big help. You are right. I need to get a manual. I was considering get the e-version of "the missing manual". What manuals do you recommend?

                • 5. Re: Multiple due date fields

                  I'm self taught starting years back and starting with a degree in Computer Science, so I am not familiar with enough different books to make a recommendation. Also, the knowledge about databases and computers you already have will help determine what's the best option.

                  You might research titles at Amazon and study the reader reviews to get a better idea as to which might be the best choice for you.

                  • 6. Re: Multiple due date fields

                    Has anyone had a problem with gaps in a serial number field? If some reason the file crashes or document is deleted there is a number that can never be used again. It's like a black hole. Is there any way to re-use the missing numbers?

                    • 7. Re: Multiple due date fields

                      Why is a gap in the sequence significant? There can be steps taken to reset the serial number if the deleted document is the last document in the series, but re-using the serial number of a document that isn't the most recently numbered record is problematic and potentially confusing. If you are doing this in a database that is shared between several users, this becomes very tricky to do as any one of the users might create a new document record at the same time that another user is deleting an existing one.

                      It may be a better idea to keep a place holder record in place that documents the fact that a document was deleted than to try to re-use the serial number.

                      • 8. Re: Multiple due date fields

                        For what it's worth, I would happily recommend 'The Missing Manual'.

                        • 9. Re: Multiple due date fields

                          Good point. We are trying to share the file and resetting the serial number could be troublesome. I guess we most frustrated by the fact we are sharing on machines that do not have static IPs which force or connections to drop, frequently. If the new record was not "auto saved" we loose that job number to the black hole. I have made a request to our IT department for a static IP address. Thanks again for the help.

                          • 10. Re: Multiple due date fields

                            You should only have one machine that hosts your file and it would benefit from a static IP address. Try changing your serial number from "On commit" to "on Create" in field options if you have not already done so. You could also script a "new record" button that creates a new record and immediately commits it. This would reduce the lost records issue.

                            • 11. Re: Multiple due date fields

                              Are you all in the same office, or on the same LAN?  Or are you all trying to connect remotely?  I don't think the static IPs will be any help if what is happening is you are dropping your connection.

                              If you drop the connection, static IP or not - let's say you were connected by wifi and walked your laptop out of range, then FM will drop the connection and not automatically reconnect when you walk back into wifi range.

                              I suspect you have another problem, as I assume you have set up the Serial Number to auto-create at record creation.  The chances of your connection dropping often enough at exactly that point that you notice the volume of numbers vanishing off into The Black Hole must be very slim.

                              • 12. Re: Multiple due date fields

                                Thanks for the responses. I just checked and my serial number field was set to Generate: On creation. Hopefully our IT team will allow us a static IP address for our station. In response to the network question.. We are in the same office and LAN. None of us are using laptops, that aren't also hard-wired connected to the network.

                                • 13. Re: Multiple due date fields

                                  Can't see how the IP issue is causing a loss of connection. I can see where you might have trouble connecting to the hosted file in some situtations--especially if using an external data source reference that specifies the IP address, but can't see how a chaning IP address on the server could break existing connections to the database as IP addresses are assigned when the computer first starts up and you won't have any connections to the server at that time.

                                  How are you sharing your database?

                                  You should use either FileMaker Pro or FileMaker Server to open and host the database on a specific machine. Other users should then use Open Remote... to link to the hosted file or files.

                                  If you are placing the database file(s) in a shared directory and opening it directly, you risk corrupting your file(s).

                                  • 14. Re: Multiple due date fields

                                    It sounds like we are doing everything you suggest. We are all running Filemake Pro 11, including the user on the host machine. The other users are opening the file from inside Filemaker using open remote function. We get disconnection messages about every 10 minutes which we can only attribution to the "non-static" IP numbers. Hopefully our IT team can get us a static IP for one of the machines.

                                    1 2 Previous Next