i thought that more ppl wud be interested .. but i guess i was wrong

This has been FIXED!.

Update:
http://symphonyoffire.com/ListX/tabid/368/Default.aspx


I am happy to announce that the ListX LP Generator is working fine now.
I have fixed many bugs and added many many features.

It now has the ability to create the LP package with few clicks and generates fancy looking pages.

It is of course free and available with the code so others can have input and suggest updates/improvements.

The release now is v0.9
It has:
- Filter, List and Inline editing/adding Template
- Export to Excel (clean export)
- Security Role handing (define who can add, edit, delete and the script will add teh code to handle it)
- Handles Files (define fields as File Uploads)

I also rewrote the initial template i had to make it easy to copy multiple lists/tabs on the same page.

by the way; you will need ListX 1.9.8 at least to be able to use. Any release before that will not work.

Please let me know if you have any thoughts for improvement.
I would like to focus on adding new templates. but teh thought is KEEP IT SIMPLE. we can not handle all the scenarios but it is good tool to get you ALMOST there. Then fine tune it to your needs.

Many Thanks to Eric Maynard. He has helped a lot in testing and debugging


Download the current MS-Access 2000 version from:
http://symphonyoffire.com/ListX/tabid/368/Default.aspx

Whoohoo!!! Thank you Talal!!! This is what I have been waiting on for over a year now. Now I can buy ListX!! I know I'll have to end up learning how to do it manually sooner or later but at least this way I can learn as I go - not nearly as boring to learn when you can build a few things along the way and see something finished once in awhile! Even if it is just a simple module.

Thanks again - keep it up!!

Greg

Talal, how do I do step 2 where I have two tables in my query for example, I want to query the userroles table to show what roles the current userid is assigned to, but in order to display the rolename, I have to join the roles table. This makes my tablename userroles, id field userrolesid but my TEXT field is roles.rolename where userroles.roleid = roles.roleid

I want to keep it simple as much as possible. So i designed it to handle the main table only by default.

Once the LP is installed, you will need to fine tune it and add other code as needed.

So we will take a minute and analyze what we need.

In your case; the main table is userroles (this is where data will be added and edited)

The joins are used normally for displaying user friendly names. For example to translate UserID to the user name and UserGroupID to the GroupName. right?

In other words it is cosmetics as far as the application concern

This is easily done by adding the joins and modifying the list's query and item.

Let me put example:

We want to add an enterface to allow users to control user roles.

Tables:
tblUserRoles
tblUsers
tblRoles

really the MAIN table here is the UserRoles (where th data will be saved)

So we will:
1. Add the table to the table definitions in the tool
2. Add the fields definitions to the tool
3. When filling up the Tab information (step 3): the tool will generate the modules in the template as soon as you choose the template (time being we only have one so click it).
4. now you will see the modules that Tab template has. Double click the Filter module
5. Add th fields UserID and RoleID. and choose s drop down list as field type.
6. As soon as you choose the field type; field type properties will be generated for you. In this case the DDL required SQL statement, Value Field and Display field (you will see them in the list and all you have to do is to fill in the values.)
In this example:
USERID Field:
SQLStatement: Select UserID, Username from tblUsers order by Username
ValueField: UserID
DisplayField (i dont recall teh exact property name on top of my head): UserName

RoleID:
SQLStatement: Select RoleID, RoleName from tblRoles order by RoleName
ValueField: RoleID
DisplayField: RoleName
7. Do the same for the Edit module (you can simply use the COPY button)
8. In the LIST module fields just choose:
UserID
RoleID
It does not really matter what field type they are because that is ignored for the list. You can actually also use the copy button from one of the other modules.
9. Run the Generator button on the Tab menu.
10. Go to where the LPs folder is, open the folder with the tab name. zip the files in that folder. IMPORTANT: do not zip the folder itself. but zip the content inside it. so that the LP engine will find the files it is looking for.
11. Import.

Now the List is propably the only please you need to modify after you have imported the Tab. so go to the List's option:
a. change the Query to read: Select tblUserRoles.*, RoleName, userName from tblUserRoles inner join tblUsers on tblUsers.UserID= tblUserRoles.UserID inner join tblRoles on tblRoles.RoleID = tblUserRoles.RoleID
b. In the Item Designer change what fields you want to show in the list:
This means change it to read [UserName], [RoleName] instead of [UserId], [RoleID].


Hope this helps.

By the way, i have added GroupValidation to the tool. a small bug now that it validates filter fields when editing/adding.
I will release this update shortly.

Would be nice to get some feedback before i put the change online.

please keep in mind:
1. I opt towards simplicity.
2. I did not intend this to be used for final cuts. It takes you 95% there and you need to drive the 5%.
3. I am not sure what ListX version 2 holds. If there will be major changes or not etc. so i am careful not to put too much work on this tool.

It helped me complete a full project for a client in a matter of hours instead fo days. <img src='desktopmodules/ntforums/images/emoticons/smile.gif' height='20' width='20' border='0' title='Smile' align='absmiddle'> I hope it helps you guys also

Just added a new version:

V 0.9.1:
- Used Group Validation (fixes incorrectly validating Filter/Edit modules at the same time)
- Corrected references to Modules

http://SymphonyOfFire.com/ListX/tabid/368/Default.aspx

Hi Talal

I think I follow the approach you're taking. Template based application generator. Been through many of those! <img src='desktopmodules/ntforums/images/emoticons/smile.gif' height='20' width='20' border='0' title='Smile' align='absmiddle'>

I set up a single table - stores with sto_pk and sto_name. I added sto_name as one of the fields. Can I specify sto_name as the "text" field as well as one of the regular fields?

I got through that OK. Then generated the LP. While uploading many things completed, but the last few things gave error messages. MyTab.Tab failed, Filter failed, Store Finder failed and edit failed. If you'd like more details, please let me know.

Thanks

Mike

can you tell me a step by step what you did?

Did you add the field to the Tab?



I am not sure what you mean by 'Can I specify sto_name as the "text" field as well as one of the regular fields?'

if you put the module in debug mode hat error do you see?

I think something is missing.

Thank you

Hi Talal

Can you send me an email at mike.yearwood@gmail.com? I would like to phone you.

Mike

Toronto, Ontario

Hey Talal !

Thanks so much for this great tool - it will save me a ton of time on the project I'm working right now!

Eric

Talal,

Great Job! Time to retire (like it isn't already) my oh so <b>Basic</b> ListX Generator.

For an idea relating to Pauldes question about related tables (foreign keys):
If there was an area to define them in Step #2, your system could use those definitions when adding one of those fields to the Filter, List, or Edit.
For the List (using the UserRoles example), instead of getting into the join business with the query, just add the Roles foreign key definition as a subquery field to the original UserRoles query like the following:

Original Query (example):
SELECT UserRoleID, UserID, RoleID
FROM UserRoles

New Query:
SELECT UserRoleID, UserID, RoleID,
(SELECT RoleName FROM Roles r
WHERE (UserRoles.RoleID = r.RoleID)) AS RoleName
FROM UserRoles

Defining these foreign keys at the table level, since almost every normalized data structure uses them, would take this tool to the 98% complete level for me! I might have to charge more per hour since I'd be so dang fast <img src='desktopmodules/ntforums/images/emoticons/smile.gif' height='20' width='20' border='0' title='Smile' align='absmiddle'>

Talal,
I'm taking a crack at this, but some clarification would be useful.

1. How should one make the connection to the data table in order to import the field definitions. I created a linked table in Access to the DNN table (which is hosted elsewhere) , but that did not do it.

2. I don't get the point of the "Name of the text field" item in the setup. what is the purpose of this? Suppose you have many text fields, what should be chosen?

thanks for any help

empluse,

Maybe I can help.

>1. How to make the connection to import.

For a database hosted elsewhere, use a connection string similar to the following:

Provider=sqloledb;DATA SOURCE=maxsql003.maximumasp.com; INITIAL CATALOG=V018U39XYZ; USER ID=V018U39XYZ; PASSWORD=2lAiufw87Sfw;

Of course, replace the SOURCE, etc. with your appropriate values from your host.

The trick that worked for me is, you must put this in the 'Custom Connection' text box of the first Application record (Step #1)... The generator reads this connection string to run the imports.

>2. What is the "Name of the Text Field" field for?

I think it's just an optional field to properly identify what record is being edited/deleted/etc. For example, if you had a table of parts with the following fields:
PartID
PartBinCode
PartName
SupplierID
DateAdded

Your 'Name of the Text Field' would be 'PartName' because it is the most appropriate field to identify to users what record you will be modifying. Not sure, but I think the generator uses this field to title some of it's edit screens?

HTH

Thanks for the reply.
I understand how to set up a connection string within DNN, it's the functionality within Access that didn't make sense. It looks like the app really needs two different strings. one to import the table structures from within Access and the "real" one that will be defined as part of the ListX module. By "hosted elsewhere" I meant in relation the MS Access instance, not in relation to the DNN installation.

empulse,

Where you able to make local Access tables (non-linked) work? I ran into the same issue recently while trying to use a table in the local Access DB.

I agree that linked tables from SQL would be great, but just being able to prototype in a separate Access file is also a plus as it somewhat shields from Builder updates.

Is this possible Talal?

Unfortunately I have been tied up with another project and have not had time to wrestle with it. Hope to get to it again Sunday. I'll let you know what transpires.

Hi Talal

I tried it out again, but continue to get errors during the load of the lp.

Step 1 - I created a new page on my site called Stores

Step 2 - I entered Stores into your Step 1 - the first page of your utility and closed the window. - Maybe a save or ok button would be better?

Step 3 - Your step 2 - I entered my table name. The name of my ID field sto_pk, the name of my text field, sto_name, and added my field names. Then I closed that window. OK button would be better again IMO.

Step 4 - Your step 3 - I filled out all the tab details. I hit the button to generate. I zipped the file. I added a listx to the stores page. I used it to install the zip (LP).

All kinds of files get created. Then tab.tab failed with this error:

System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'IX_TabPermission'. Cannot insert duplicate key in object 'dbo.TabPermission'.

Filter failed with this error:

System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'IX_ModulePermission'. Cannot insert duplicate key in object 'dbo.ModulePermission'. The statement has been terminated.

Stores failed with this:

System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'IX_ModulePermission'. Cannot insert duplicate key in object 'dbo.ModulePermission'. The statement has been terminated.

Edit failed with:

System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'IX_ModulePermission'. Cannot insert duplicate key in object 'dbo.ModulePermission'. The statement has been terminated.

Hope you can help!

Thanks

Talal

Just FYI, but I just tried to install the Doctor Management LP to see if this was something specific to you, and it failed loading the Doctor Management.tab with this error:

System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'IX_TabPermission'. Cannot insert duplicate key in object 'dbo.TabPermission'. The statement has been terminated.

I guess it's not your stuff!

Mike

sowwi guys, i do not get email notification for these msgs so i missed them all.. i will try to respond when i am back home

by the way:
Violation of UNIQUE KEY constraint ..

means that you need to get the LATEST update from ListX which i believe was 1.9.8.8 or something