Alpha Video Training
Results 1 to 7 of 7

Thread: Filtering data by date with a set.

  1. #1
    Member
    Real Name
    Rodney Efird
    Join Date
    Apr 2000
    Posts
    137

    Default Filtering data by date with a set.

    Hi guys.
    I have an app that is going to have a payroll system and I am having problems getting the data to filter correctly. The system is for the trucking industry and will have loads done by the driver as well as advances taken by the driver. I need to filter using the dates of the pay period. I may have 10 loads and 2 advances. The loads and advances are in different tables. How can i get the data to show for the pay week date? I am probably over looking a very simple solution but it escapes me.

    Thanks for the help.

    Rodeny

  2. #2
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default RE: Filtering data by date with a set.

    Rodney, check out the between() function. Using it and the beginning and ending dates of a payperiod you ought to be able to be able to filter the records in both the loads and advances tables.

    -- tom

  3. #3
    Member
    Real Name
    Rodney Efird
    Join Date
    Apr 2000
    Posts
    137

    Default RE: Filtering data by date with a set.

    Thanks Tom. I will try your suggestion.

  4. #4
    Member
    Real Name
    Rodney Efird
    Join Date
    Apr 2000
    Posts
    137

    Default RE: Filtering data by date with a set.

    I need to clarify a bit. I need to link the tables in the set to the header table by dates that are not necessarly the same in the tables. The problem is that the tables must corrospond with a driver name.

  5. #5
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default RE: Filtering data by date with a set.

    I see the problem.

    I don't think you can run a query against the child tables from within the set. You probably have to use the flattenquery() function, instead.

    Your problem would be greatly simplified if you could consolidate the two child tables into a single one. Call it transactions and have fields for loads and advances both. It would resemble a payments table, with payments and refunds fields. With a single child table, you could define a second set, inverting the original order of the tables, so that the new child table is the parent and vice versa. Printing the report then is trivial, just filter the new parent table by the date range you want and go.

    -- tom

  6. #6
    Member
    Real Name
    Rodney Efird
    Join Date
    Apr 2000
    Posts
    137

    Default RE: Filtering data by date with a set.

    Tom, it would be much eaiser to use a single table but the advances come by a report. The report comes on say wed and the data is input in a browse so that all of the info can be input at a single location. The advances are not listed against specific loads. I really need to be able to link the tables by mismatched dates.

    Thanks for your suggestion.

    Rodney

  7. #7
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default RE: Filtering data by date with a set.

    I didn't mean to suggest that the advances would be matched or linked to the loads. The only thing the two records would have in common would be the driver's name or id.

    A typical 'transaction' record would have fields like this:

    DriverName
    DriverID
    Advance (quantity)
    Load (quantity)
    Transaction Date


    You would enter separate records for each transaction, whether it was to note an advance or whether it was to note a load.

    -- tom

Similar Threads

  1. Date field in imported data
    By Alan Amos in forum Alpha Five Version 4
    Replies: 2
    Last Post: 02-24-2004, 04:20 AM
  2. problem filtering with date variables
    By Jeff Pearson in forum Alpha Five Version 5
    Replies: 1
    Last Post: 04-25-2003, 07:11 PM
  3. Index or filtering a date range
    By James Gatton in forum Alpha Five Version 1
    Replies: 2
    Last Post: 07-26-2001, 03:44 PM
  4. counting date fields w/no data
    By Wanda Tucker in forum Alpha Five Version 4
    Replies: 8
    Last Post: 05-14-2001, 12:03 PM
  5. Browses showing different date data
    By Rodney Efird in forum Alpha Five Version 4
    Replies: 0
    Last Post: 04-12-2000, 12:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •