1 2 Previous Next 15 Replies Latest reply on Aug 24, 2012 9:52 AM by philmodjunk

    One entry per month--the last for each month

    ultranix

      Title

      One entry per month--the last for each month

      Post

      One entry per month--the last for each month?

      What an interesting challenge....

      I'd use this calculation to give all entries of the same month an identical value:

      cMonth: DateField - Day ( Datefield ) + 1

      this is the date for the first day of the month for each month.

      Then you can define a self join relationship based on cMonth on this table to match each record to all other records of the same month and year.

      Max (SelfJoinOccurrence::DateField ) = DateField

      will return True or the number 1 for the last record of any given month and thus you could put that calculation into a field named cLastOfMonthFlag and use

      PortalTable::cLastOfMonthFlag as part of the filter expression to filter out all but the last entry of each month.

      Could you explain it in more detailed way?

      I created cMonth field, it returns first day of each month, that is fine.

      As I use portal, so then i could maybe avoid creating relations and use Filter portal calculation instead.

      I tried this one: cMonth = cMonth and Max(Datefield) = Datefield, but it's not working, as it shows all records in the portal.

        • 1. Re: One entry per month--the last for each month
          philmodjunk

          To use a portal will require Some sort of relationship so you might as well make it the one I suggested.

          Then use the portal filter expression I suggested, OR

          Don't use a filter. Don't use the Max function (could slow things down anyway).

          Instead:

          Sort your portal in descending order by date. Make this a one row portal and you'll see the last date entry for the same month in the portal.

          • 2. Re: One entry per month--the last for each month
            ultranix

            Let's make it clear:

            There's 1st field cMonth (calculation:  DateField - Day ( Datefield ) + 1)

            and then what's that "Max (SelfJoinOccurrence::DateField ) = DateField"?

            Filter portal expression?

            I just don't quite get it.... Could you give a step-by-step info with making it clear - where it's field, where it's relationship, and where it's expression. It might look obvious for a programmer, but my strength is design and idea side, while fulfillment obviously is inferior.

            My portal contains of 20 rows and i cannot shrink it to a single row.

            Basically, I update my progress every week, so 1 record per week, 4 per month, more than 50 in a year. I want to be able to view last record of each month, so 12 records of each year should be displayed only.

            Then, after I manage to deal with this technique, I'm going to add another tab and have yearly progress, where I only see one entry per year - the last (or the current, if the year is current).

            • 3. Re: One entry per month--the last for each month
              philmodjunk

              I originally suggested

              Max (SelfJoinOccurrence::DateField ) = PortalTO::DateField

              as a portal filter expression. The syntax wasn't quite right, the term to the right of the = sign needed a table occurrence name, but you don't really need it if you sorted your portal like I suggested.

              My portal contains of 20 rows... so 12 records of each year should be displayed only

              What would you do with the other 8 rows? I was thinking in terms of one "last month" record displayed for each goal record on your layout. 12 one row portals could work, but a list view where each set of info is on it's own record might make for a better structure--that precludes real tab controls though. (many DB's simulate tab controls with a list view by using tab shaped buttons that change layouts.)

              I can't really provide a step by step here as I'm not clear on the structure of your data and have suggested several approaches to get what you want.

              • 4. Re: One entry per month--the last for each month
                ultranix

                If i set up a file draft to only implement this feature, would you mind taking a look?

                • 5. Re: One entry per month--the last for each month
                  philmodjunk

                  Sure, but please be patient. downloading, analyzing and responding back takes more time than just posting quick fixes in this forum so I tend to put those as last task to do when participating in the forum. Might take a day or two to get back to you.

                  • 6. Re: One entry per month--the last for each month
                    ultranix

                    I sent you an email with a very simple working file and explanation, day or two sounds good, if the issue is solved.

                    Thanks in advance.

                    • 7. Re: One entry per month--the last for each month
                      ultranix

                      thank you very much, it seemed i would never get through this without your help.

                      i was able to copy what you did on example file to my project. it worked fine (with some conditions which i will state later). I also added cYear (calculation: date - DayOfYear(date) - 1) and cLastYearFlag fields (and relation: EVENTS::cYear = EVENTSSameYear::cYear to achieve one record per year - last/current for each year.

                      here's what interesting with both monthly and yearly views and is perfectly seen on the example file i sent you: (i only tested with last entry, i.e. latest date, that is the max date of the progress entries)

                       - if i set latest date to 12-09-11 to both GL0001 and GL0002 (id_goal field) entries in EVENTS table, it would show properly that entry as the last/current entry for the month of september in monthly view and for year 2012 in yearly view.

                       - but if i set one date to 12-09-11 and another to 12-09-10 - it would only display the max date (12-09-11 in this case) correctly. and in the progress portal of the goal with 12-09-10  - no entry would be displayed for month of september and no entry for the year 2012.

                      how it would be possible to correct that?

                      • 8. Re: One entry per month--the last for each month
                        philmodjunk

                        no entry would be displayed for month of september and no entry for the year 2012

                        Do you have any records dated September, 2012?

                        IF not, there is no record that meets the filter criteria and I don't know what you would want to see in that case.

                        • 9. Re: One entry per month--the last for each month
                          ultranix

                          well, actually, it wasn't for september of 2012, i just entered new record in that example file i sent you after i tested with my own database.

                          things are it works like charm with current goals, meaning that it displays properly all weekly, monthly and yearly goals.

                          but when it comes to cancelled or reached goals, that's where the problems arise.

                          for example, i had a goal "to earn X amount". which i cancelled on 2012-07-05 and from that 2012-07-14 i have another goal "to earn X+ amount" (different amounts, different id_goal values).

                          and for that cancelled goal the last entry is of 2012-07-05. for weekly view - everything is displayed correctly, as no additional filtering is required.

                          for monthly view - the last record is of 2012-06-30. (all other months prior to that are displayed correctly, just no entry with 2012-07-05)

                          for yearly view - the records of 2010-12-28 (last of 2010) and 2011-12-31 (last of 2011) are displayed. but again, no entry with 2012-07-05).

                          • 10. Re: One entry per month--the last for each month
                            philmodjunk

                            From here, that seems like the correct results. To repeat, what would you want to see? The event records from a goal record that has a completely different ID number? How would the database "know" to do that?

                            You'll need to come up with a method that updates the event records with the cancelled ID to have the ID of the new Goal. A script with Go To Related Records followed by a Replace Field Contents is one option.

                            Duplicating the Goal record. "Cancelling" the new copy and then changing the original goal to reflect your new amount is another option and one that will keep it linked to your exisiting event records. A "cancel" button could perform a script that does this fairly easily.

                            • 11. Re: One entry per month--the last for each month
                              ultranix

                              I am sorry for messing my explanation up with active and cancelled goals. No, i don't want to display goal records that has completely different ID number.

                              I said that the last progress entry of "cancelled" goal (id: GL0003) is with date 2012-07-05 (earlier entry dates: 2012-06-30, 2012-06-23, etc.). so, correct display results for month view would be 2012-07-05 (last and only entry of july), then 2012-06-30 (last entry of june) etc.

                              and for yearly - instead of just displaying records of 2010-12-28 (last of 2010) and 2011-12-31 (last of 2011), it should also display the record of 2012-07-05 (the last entry of 2012, and instead - no records of 2012 are displayed).

                              id:goal, i.e. GL0003 is the same for GOALS table and EVENTS table, relation, as you know is GOALS::id_goal = EVENTS::id_goal, so i only want to display the records that match goal_id from EVENTS table.

                              • 12. Re: One entry per month--the last for each month
                                ultranix

                                here's a link of screenshot. i hope it would explain way better.

                                http://i771.photobucket.com/albums/xx358/UltraNIX/fmpscreen.jpg

                                • 13. Re: One entry per month--the last for each month
                                  philmodjunk

                                  The screen shot doesn't help because it just shows that the data is missing not what data is actually in your table and what values are in the date and ID fields. It also doesn't show what relationships, portal filters and calculations were used. This worked, as far as I can tell, in the demo file I sent back to you.

                                  I accidentally posted "display goalrecords that has completely different ID number", a typo I corrected after posting to say Event records but such corrections won't show in your email. If you read my post again, I asked if you wanted the events for a cancelled goal to now be listed as events assigned for the new goal that replaced it.

                                  I'm really not clear on what the status of a goal has to do with what event records show in the portal. The modified copy of the file that I sent you totally ignores the status, so cancelled and other status goals are all treated exactly the same. The portal matches events to Goals by GoalID and the a portal filter filters out all but the last monthly entry for each mont.

                                  • 14. Re: One entry per month--the last for each month
                                    ultranix

                                    Status has nothing to do, I agree, because I'm not using status field in either calculations/filter portal expressions or relations between table occurencies.

                                    And about cancelled goals, I don't want to do anything about them from duplicate, copy, etc. perspective. If i cancel a goal and enter a new one, i would like to see the progress of the new goal and cancelled goal separately. that is already achieved.

                                    If you still have the copy of the file you sent me, you could easily check what i'm talking about in adding additional record via portal (first enable "Allow creation of records via relationship in EVENTS table" for easier input) to both goals. First choose whatever date of september (let's say 2012-09-08) and for the next record choose date + 1 (in this case - 2012-09-09).

                                    That 2012-09-09 would display in monthly view as a record of September and that 2012-09-08 wouldn't, despite it should as it's only/last entry of september.

                                    I'm not saying that i enter goals' progress on different dates. I usually do my weekly reviews on saturday, so all dates match, and throughout the database there's no such thing as progress of the then/now-current goals entered different days of the week.

                                    What I'm saying, and what's actually causing problems: If I cancel a goal on, let's say 2012-08-14 and there are progress entries for other goals on 2012-08-21 and 2012-08-28 - those two latter would show up correctly in the monthly view, displaying the one last record (2012-08-28) for August, but for the goal that was cancelled on 2012-08-14, it wouldn't show up any entries for the month of August, because, to my mind (i cannot find any other logical explanation), that Max function counts the max date of the month, that would, in this case, be 2012-08-28 and only this gets cLastMonthFlag result "1", and all others are ignored.

                                    To solve this up I'm contemplating of the idea - add another calculation field with Case statement, which would use max function of all the progress entries of current goal regardless of the month and if cLastMonthFlag field for that record is empty, it would return "1", otherwise it would display cLastMonthFlag field's result (that would be "1" as well, if it worked correctly).

                                    And then I would filter portal not by EVENTS::cLastMonthFlag but by my newly created calculation.

                                    EDIT: My contemplation is now turned into results that work like a charm.

                                    Thanks Phil for assistance, now i'm happy with the solution as it is.

                                    1 2 Previous Next