1 2 Previous Next 16 Replies Latest reply on Apr 12, 2012 3:20 AM by expediter

    Combine 2 date fields into one field or list

    expediter

      Title

      Combine 2 date fields into one field or list

      Post

      I am really new to filemaker and struggling. I have two date fields that need to be tracked for two separate deadilines. I'd like to be able to combine the two fields to come up with a list of all deadline dates that could be sorted as one list. I tried using the list function referencing the two date fields (both in the same source table) but the results only listed one of the date fields. Does anyone know how to accomplish this?

        • 1. Re: Combine 2 date fields into one field or list
          philmodjunk

          List ( DateField1 ; DateField2 )

          Should work, but your valuelist will be of Text, rather than dates so it will work as a source of data for your value list, but not as good way to work with your data as actual dates.

          • 2. Re: Combine 2 date fields into one field or list
            expediter

            Is there a better way to do this? I have tried the List as you mentioned. It returns both dates into the same field value and then lists the two dates in this format "4.102e+12". When I click on the field it shows both the dates but this format is not useful for my purpose of displaying the values from both fields in one list. Any other ideas I could pursue?

            • 3. Re: Combine 2 date fields into one field or list
              Sorbsbuster

              Could you clarify: "I'd like to be able to combine the two fields to come up with a list of all deadline dates that could be sorted as one list."

              Do you mean that you would like to see, for example, all the records that are close to Deadline Date A, and also all the records that are close to Deadline Date B, and then sort them by Person Responsible, for example?

              If that is the case you don't need to combine the dates.

              • 4. Re: Combine 2 date fields into one field or list
                philmodjunk

                Perhaps:

                List ( GetasText ( field1 ) ; GetasText ( field2 ) )

                Before trying that, first make sure that text is specified as the return type for your calculation, then try the above variation if you still can't see two dates separated by a return. (If your field is only 1 row of text tall, you'll have to click into the field to see both dates.)

                • 5. Re: Combine 2 date fields into one field or list
                  expediter

                  I tried your suggestion and I'm getting the same result as with my first List attempt. I'll try to better explain what I'm trying to do. I have a print order form for jobs that will be mailed. Usually there is only one mail date but occasionally there is a second mail date of the same material. I want to be able to include Mail Date 1 and Mail Date 2 in the same field so I can sort for deadlines and see how many deadlines fall on each day. And I need to have 4 or 5 other related fields in my layout such as Job name, Job number, Ship Date 1, and Ship Date 2 that will accompany the list so I guess I would have to sort all of them together? Now that I spell this out, perhaps it's more complicated than I thought since there are related fields that need to be included. Any help you can offer would be SO appreciated. I have looked for solutions in the Knowledge Base but I haven't found something that quite describes my needs.

                  • 6. Re: Combine 2 date fields into one field or list
                    expediter

                    I forgot to mention that when I tried your solution only one number showed up in the field, even when I made it tall enough for several numbers.

                    • 7. Re: Combine 2 date fields into one field or list
                      philmodjunk

                      The method I describe will produce a list of two values if you have values in both fields. If it doesn't, you need to check the actual values in the two fields and also the return type specified for your calculation field. But it's really not the best option for what you are trying to do here.

                      I want to be able to include Mail Date 1 and Mail Date 2 in the same field so I can sort [search] for deadlines and see how many deadlines fall on each day.

                      A related table of mail date entries may well be a much more flexible approach than two separate fields. You can then enter find mode, enter the date or a range of dates (such as 4/1/2012...4/15/2012] and perform your find. This will then find all records with at least one related record with a mailing date in that range. You get an added advantage of being able to have as many mailing date entries as you need simply by adding related records on an as needed basis.

                      You can also enter find mode, enter the date or date range into Mail Date 1, then use the Requests menu to make a new request, enter the same date or date range into Mail Date 2 and perform your find. This will find all records with a date in either Mail Date 1 OR Mail Date 2 that fall on the date or date range.

                      These types of finds can be scripted.

                      • 8. Re: Combine 2 date fields into one field or list
                        expediter

                        I tried what you said and it did return the find in Mail Date 1 and Mail Date 2 in their repective field columns on Table View that can then be sorted by ascending date. What I am looking to be able to do is to integrate these two finds so that I can have one report showing just one column of mail dates in sequence for a particular range. Is there a way to accomplish that?

                        • 9. Re: Combine 2 date fields into one field or list
                          philmodjunk

                          I'd need to know more about the structure of your database and why you need two separate date fields. From what little I know at this point, a related table with two records for these dates would give you your single column of dates.

                          • 10. Re: Combine 2 date fields into one field or list
                            Sorbsbuster

                            How would you expect these records to be sorted?

                            Record      Mail Date 1        Mail date 2
                            A                1/1/2012          15/2/2012
                            B                1/2/2012          12/2/2012
                            C              15/2/2012          31/5/2012
                            D                1/1/2012          31/3/2012
                            E                1/1/2012          15/3/2012

                            The idea of a related table with one record for each date means that you can sort them all independently, as 10 separate events, which is what they are.

                            • 11. Re: Combine 2 date fields into one field or list
                              expediter

                              I think I am doing a very poor job of explaining what I need to do. I have a Project number and associated with that, a mailing number (MAIL DATE 1). And just sometimes there is a second mailing date (MAIL DATE 2) associated with the same job number. Maybe this would not have to be in the same column but it does need to be in sequence. I would like to find records within a certain date range and listed by Job Number and date. Since each job number only has one maiing per date field (either MAIL DATE 1 or MAIL DATE 2) I would like to be able to produce something that looked like what is below in #3. The top portion is as it would look before sort, then #2 is after sort. But #3 is what I need where each line is a mailing date in date sequence and identified by Project Name (and a few other fields once I figure out how to do this). This is in essence a schedule for my mailhouse so that we can coordinate with maiing.

                              Is this more like a calendar solution? Could I create a scrpt that would search for all the items on a particular date and put them in rows, then another for the next date, then another? Is there any kind of report that could yield this type of result? The scripting sounds like a lot of work up front but this schedule is pivotal to my business and I am willing to invest any amount of time it would take to automate this process because right now I have to transcribe the schedule which takes a large chunk of my time. I am open to any Filemaker solutions you think would work.

                              • 12. Re: Combine 2 date fields into one field or list
                                expediter

                                I uploaded a PDF but I see that doesn't show up on my screen in the forum. The instuctions say that the image will be uploaded when I "click Save below" but the only button to click on my screen is "Post Answer".  I will try to upload it here again and see if the Save shows up this time but if you don't see anything it's because it did not show up again. Thanks!

                                • 13. Re: Combine 2 date fields into one field or list
                                  philmodjunk

                                  Can't see any uploaded image here. Check the info below the Post A Answer box to make sure that you are uploading a compatible file type or it will not appear.

                                  What you describe so far, sounds like you either need two job records or a table of related Mail Date records like we have recommended to you.

                                  • 14. Re: Combine 2 date fields into one field or list
                                    expediter

                                    You were right Phil. I converted my PDF to JPG and will upload now. 

                                    I did try the table of related Mail Date records but when I tried the sort as you described, I still only ended up with 2 columns of dates, sorted independently of each other. I would really like to keep one project number for the entire job as the printer, mailhouse, trucker and interoffice accounting staff all use that number as reference. I could see if there several maiings per project but as it stands now there is only one mailing 90% of the time and then if there is another mailing, it's just one additional. Perhpas I will have to do an additional job order as a supplemental mailing order but I think I would get some kind of calendar program before doing that since it would mean changing a lot of other structure to accommodate such a change. Here's go trying to send a graphic that illustrates what I'm looking for.

                                    1 2 Previous Next