5 Replies Latest reply on Feb 3, 2015 9:53 PM by philmodjunk

    Generating report or a list of dates for which I've no record

    Vicspidy

      Title

      Generating report or a list of dates for which I've no record

      Post

      I've two tables artist and report where each artist enters multiple data against a date.

      I need to generate a report or list of dates agianst which artist haven't entered any record. How to accomplish this? Can anyone help?

        • 1. Re: Generating report or a list of dates for which I've no record
          SteveMartino

          In the field that you want to search that is empty

          Enter Find Mode and type an = sign or a == sign in the field.  Then perform find will find all the records where that field is empty.

          If it gets you the records you are looking for, then you would script this to perform the find and go to the report layout to show the results.

          Although I'm concerned about your db setup.  You shouldn't need a different table for reports.  Should be a different layout from the same table.  I don't understand what artist do when they enter multiple data against a date.  Are they projects?  If so you would need and Artist Table and a Projects Table

          • 2. Re: Generating report or a list of dates for which I've no record
            Vicspidy

            But I've only 5 records in the report table for the month of january, on different dates. Then I need to present a list of dates for which which he has no entry

            • 3. Re: Generating report or a list of dates for which I've no record
              philmodjunk

              Sounds like you'll need a related table with one record for each day of each month of the year. You can match against that table and produce a found set of dates in the related table not found in the original table.

              • 4. Re: Generating report or a list of dates for which I've no record
                Vicspidy

                Yes Phil, I also thought to approach it the way you mentioned but I think wouldn't it be a little bit complex way to do it? Is there any possibility of doing it any easier way, Also if it is not possible, I wonder how to approach it the way you told. How can I generate data in a table for the whole year. Also it does seem counter intuitive way to do it.

                        I've a thought to do it like this, I would present the user to select a date and then I'll have to find dates which are not there between the selected date and current date. But I don't  have a clue how to do this.  

                • 5. Re: Generating report or a list of dates for which I've no record
                  philmodjunk

                  But do you have an alternative?. The relationship might be set up like this:

                  DatesTable::cMonth = WorksTable::cMonth AND
                  DatesTable::cDay = WorksTable::cDay AND
                  DatesTable::gArtistID = WorksTable::ArtistID

                  You need only create a table of 365 records to get your list of dates for every day that the artist did not log a record in works.

                  cMonth can be defined as Month ( dateField ). cDay as Day ( DateField ). gArtistID would be a global field. Specify the artist ID, perform a find for all records in Dates Table in a particular date range with an omit request to omit all records that have a related record in WorksTable.