[SOLVED]Generate Records using a loop

Jhstephenson

I have a Dates BO that I want to generate records for by entering a Beginning Date and an Ending Date.

I want to increment the beginning date by 1 until it reaches the end date and generate a record for each of the dates.

If that makes sense to anyone, how can I do that in Aware?

I found a MySql script that will do it, but it assumes the Date is the Primary Key and does not have an ID number in it. When it runs it errors out because the Aware BO is expecting a ID to be assigned to each record.

Here is that SQL:

INSERT INTO `mfdates` (`date`
    , `day`
    , `month`
    , `year`)
SELECT
    DATE
    , INCREMENT + 1
    , MONTH(DATE)
    , YEAR(DATE)
FROM
    -- Generate all possible dates for every year from 2001 to 2100.
    (SELECT
        DATE_ADD(CONCAT(YEAR, '-01-01'), INTERVAL INCREMENT DAY) DATE
        , INCREMENT
    FROM
        (SELECT
            (UNITS + TENS + HUNDREDS) INCREMENT
        FROM
            (SELECT 0 UNITS UNION
            SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
            SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
            SELECT 7 UNION SELECT 8 UNION SELECT 9) UNITS
        CROSS JOIN
            (SELECT 0 TENS UNION
            SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION
            SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION
            SELECT 70 UNION SELECT 80 UNION SELECT 90) TENS
        CROSS JOIN
            (SELECT 0 HUNDREDS UNION
            SELECT 100 UNION SELECT 200 UNION SELECT 300 UNION
            SELECT 400 UNION SELECT 500 UNION SELECT 600 UNION
            SELECT 700 UNION SELECT 800 UNION SELECT 900) HUNDREDS
        ) INCREMENT
        -- For every year from 2001 to 2100, find the number of days in the year.
        , (SELECT
            YEAR
            , DAYOFYEAR(CONCAT(YEAR, '-12-31')) - DAYOFYEAR(CONCAT(YEAR, '-01-01')) + 1 DAYS
        FROM
            -- Generate years from 2001 to 2100.
            (SELECT
                (2000 + UNITS + TENS) YEAR
            FROM
                (SELECT 0 UNITS UNION
                SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
                SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
                SELECT 7 UNION SELECT 8 UNION SELECT 9) UNITS
            CROSS JOIN
                (SELECT 0 TENS UNION
                SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION
                SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION
                SELECT 70 UNION SELECT 80 UNION SELECT 90) TENS
            ) YEAR
        WHERE
            YEAR BETWEEN 2015 AND 2100
        ) YEAR
      WHERE
          INCREMENT BETWEEN 0 AND DAYS - 1
      ORDER BY
          YEAR
          , INCREMENT) DATE;

tford

From the User Guide:

CREATE Action
The CREATE Action creates an instance of the specified business object. For example,
CREATE Account
CREATE action can optionally initialize one or more attributes of the business object that
it creates, for example:
CREATE Account WITH Account.State='Open', Account.Balance=0
The attributes to be initialized must follow the WITH keyword and must be separated by
the comma. An initialization expression may contain any constant or other calculation.
Sometimes it may be necessary to create several instances of a business object with a
single CREATE action. For example,

CREATE Transaction FOR EACH Account
CREATE Transaction FOR EACH
DAY/WEEK/MONTH/QUARTER/YEAR/WEEK_DAY/WEEKEND_DAY BETWEEN
Account.OpeningDate AND Account.ClosingDate
CREATE Transaction FOR EACH NUMBER BETWEEN 1 AND 3
CREATE Attachment FOR EACH FILE IN 'c:/mydirectory'
The action in the first example will create as many instances of the Transaction object as
there are instances of the Account object in the Context.

Jhstephenson

Thanks that worked.