Creating a Template to make a Table

[MoGrace]

In my online payroll program there is this feature I want to duplicate in my desktop app. Classification codes are set up for the Company by adding records in a table. These records then become fields in the Employee Classification table where checkboxes are used to select them for each employee. The Company classifications can then be selected as a master for filtering reports and record displays for Employees.

In my payroll program the user can change, add or delete the Company codes and those changes will be reflected in the Employee table.

The Company records look like this (I am assuming the IDCODE exists too):

IDCODE CKBOX CLASS
101 T/F Current PR
102 T/F Salaried
103 T/F Terminated

There would have to be a limit to the number of records in the Company table like maybe 20 or 30 at most. In the Payroll progam the Company template shows all the fields as blank and any that are not filled in are not added to the Employee table.

So I am guessing that the Employee Classification fields will look like this; plus adding a field to link to the Employee Data table:

FILENO
CKBOX101
CLASS101
CKBOX102
CLASS102
CKBOX103
CLASS103

That's about as far as I have gotten on how this would work. Any ideas?

[Stan Mathews]

Re: Creating a Template to make a Table

I'm not clear on what your question might be. There are methods like

TABLE.ADD_FIELDS()
TABLE_RESTRUCTURE()

etc

which will allow you to modify tables "on the fly".

[MoGrace]

Re: Creating a Template to make a Table

I'm not clear on what your question might be. There are methods like

TABLE.ADD_FIELDS()
TABLE_RESTRUCTURE()

etc

which will allow you to modify tables "on the fly".

Hi Stan,
I am not real clear on what I am doing either :o

I added 20 records to the Master and filled in 15, so five records have blank CLASS fields. In the form w/ embedded browse used to change these, all 20 records display and no more can be added.

In the EE CLASS table, I used the fields like in my post above but without any CLASS### fields. On that form, I am using lookup calcs to get the Class names from the Master table so changes made there will show for each employee record. Also if any of the calcs are blank then no checkbox appears for selection.

Now all I need to do is add a EE_CLASS record whenever I add a new employee. So far this is not too hard.

What I want to do with this info is be able to create a query to filter the display of Employee records based on the selections in the Master table that match the EE_CLASS records.

The set to display the form is structured like this:
Employee
==>Co_Class (using a Link field of 'X')
-->EE_Class (Link on FILENO)

Since EE_Class is a child of Employee, I am not sure how to write the query for the browse that displays the Employee list. Also some filters would not work together - like Current PR and Terminated since that would show All records.

Hopefully this makes more sense for what I am trying to do.

[MoGrace]

Re: Creating a Template to make a Table

I changed the structure of the co_class table to match the structure of the ee_class table. The Set now looks like this:

EMPLOYEE
-->Mstclass
-->EE_class (linked on FILENO)

How do I write a query that would match all the ee_class records marked as .t. with the (one) mstclass record marked .t. and have it filter the Employee records? I did try the Query by Expression builder which lets me match the fields from both tables but it didn't give me any filtered results. I am definitely missing some concept here.

If mstclass->chk04 = .t. then find where eeclass->chk04 = .t.

[Tom Cone Jr]

Re: Creating a Template to make a Table

Robin, show us the current structure for the Mstclass and EE_class tables.

For the Mstclass table explain what the fields mean if their names aren't self-evident.

Are the links one to one or one to many (for Mstclass, and for EE_class)?

It's sounding like you want to search horizontally within the fields of each record, instead of vertically through the records themselves. i.e. return those records where any of several fields contain a specific value. This kind of query is more difficult. Alpha Five is optimized to search vertically through the records... not horizontally through the fields, if you see what I mean. If I'm on the right track you should consider restructuring the Mstclass table so that instead of a series of fields for each possible classification (any one of which might be set TRUE), you have a single field, within which you store the classification field value itself. It's the field values that are important. Not the fieldnames. If you have a single field in some records it will be set to "Terminated" in others, "Salaried", in others "current PR", etc. Your search for "salaried" employees becomes easier to construct.

[MoGrace]

Re: Creating a Template to make a Table

Hi Tom,
Well I have tried it both ways now and have not been able to get either way to work. But I think you are right and my first example makes more sense to me too. I think because I am trying to do the query on the primary through the child table linked to it one to one based on fields that match from another table I have overwhelmed myself here.

If the EE_Class fields were contained in the Employee table, I know that would work, but I didn't want to add 40 more fields to that table.

[MoGrace]

Tom - here's my query

This actually works the first couple of times I push the button, but then I get an error. Is there a better way to write this query? I tried opening a 2nd instance of the table just to run the query but it didn't like that either.

EDIT: Here is how I fixed it without a query:

 
dim ar[20] as p
DIM recs as n = 0
[COLOR="Navy"]'GET COUNT OF RECORDS MATCHING FILTER[/COLOR]
[COLOR="Purple"]recs = tablecount("co_class","CHK=.T.")
if recs = 0 then
    ui_msg_box("No Class Selected","Please Select a Class to find",0)
    end
end if[/COLOR]
[COLOR="navy"]'BUILD THE FILTER EXPRESSION FROM THE ARRAY[/COLOR]
ar.initialize_from_table("co_class","CHK=.T.")
DIM i as n
DIM q_fltr as c
i = 1
FOR i = 1 to recs 
 if i = 1 then
[COLOR="Navy"]'This creates the matching field name in 1:1 linked table[/COLOR]  
  q_fltr = "EE_CLASS->CHK"+ar[i].id+"=.t."
 else
  q_fltr = q_fltr+".and."+"EE_CLASS->CHK"+ar[i].id+"=.t."
 end if
NEXT
trace.WriteLn("Filter Exp: "+q_fltr)
[COLOR="navy"]'RUN THE QUERY AND DISPLAY IN BROWSE[/COLOR]
recs_found = topparent.queryrun(q_fltr,"","","No","<Cross Level>",.F.)
if recs_found <=0 then
    topparent.Show_all()
    ui_msg_box("Query Results","No Records Found",0)
end if
browse1.fetch_first() [COLOR="navy"]'employee records - parent[/COLOR]
browse2.fetch_first() [COLOR="navy"]'co_class records 1:N[/COLOR]

[Stan Mathews]

Re: Tom - here's my query

Try changing INDX.drop() to

parentform:tables:co_class.query_detach_all()

[MoGrace]

Re: Tom - here's my query

Thanks Stan I will try that!

Oops that generates an Error 1051. Where do you find these error codes explained?

Found it (I am getting better at searching in this forum!)

"Active relation uses that index"

My other error in the script above - 1003 is "No such field" and I have no idea why it tells me that.

[Stan Mathews]

Re: Tom - here's my query

Thanks Stan I will try that!

Oops that generates an Error 1051. Where do you find these error codes explained?

Found it (I am getting better at searching in this forum!)

"Active relation uses that index"

My other error in the script above - 1003 is "No such field" and I have no idea why it tells me that.

Different flavor of the same error. If the index/query is in use, you can't drop or detach it.

[MoGrace]

Re: Tom - here's my query

Stan,
I didn't need a query in the first place since TABLECOUNT() will get me the records in the filter without opening the table at all.

Sometimes there is just too much information to remember...

I edited the button script above and it works great. All I need is some user prompts for mix matched selections.