I am working on a Project which needs to have a relationship in Three Databases

1) Members DB
2) Orders DB
3) Order Processing DOC DB

Members post orders which are processed with some more inputs.

I have worked upto a 2 way relationship i.e. Members and Orders J P DENI, I need your help now.

Regards

Vijay

Quick and dirty advice...you will need to add another sub in your html.pl file called sub switch_to_process.

This sub should look similar to your other switch subs.

Then in your sub html_record routine, you will have to add the following codes:



So, the complete codes should look like:

Change ID to the shared $db_key between the databases.

Then to print values from the processed order database, use the following codes:

Change FieldName to the name of your fields.

Hope this helps.

Regards,

Eliot Lee
Anthro TECH, L.L.C
Web: http://www.anthrotech.com/

So your relationships would be as follows:

One member can have many orders
One order can have many processings(?)

Right?

If so, just do what you did before to set up the relationship between the members and the orders dbs.

If you have specific questions, be sure to ask.

JPD

Hi JP Deni,

Let me explain my project in brief.

I am creating an Sales Order Processing System for Distributor of Computers.

My Client has 10 Branch offices and each branch handles about 20-30 dealers who in turn sell computers to end users.

My client wants a system by which dealer should be able to log into the site and place order for the PC's. So that makes 2 databases
1) Dealer
2) Order
So relationship is one dealer places multiple orders ( One to many relationships)
This essentially is to pickup the dealer info while placing the order. and also to track the order status later.

Now comes Sales Order Processing procedure.
My client's BRANCH OFFICE creates a document called SOPF (Sales Order Processing Form) which is essentially collected data from "dealer" and "Order" database so the third database called
3)SOPF
The relationship is as follows
1 dealer - Many Orders
1 Order - One SOPF

SO this sales order processing Form is essentially separate for each order which has the dealer info, Order Info plus some additional information for administrative purpose.

I had discussed this earlier with you and with your help I could get into the first two databases.
You have also solved my date problem which i had posted in different thread

So is the picture clear ?






Regards

Vijay

I see. And you're trying to figure out how to add the data from the other two databases into the form. Yes?

The first thing you want to do is to make your SOPF .cfg file. It will make it easier on you if you keep the names of the fields consistent where they overlap. (If a field is "DealerName" in the dealer.cfg file, make it "DealerName" in the SOPF .cfg file. BTW, since there is a one-to-one relationship between the orders and SOPF dbs, you might as well use the same key value for both, including the same name. When you set up your SOPF .cfg file, set $db_key_track=0; so the script won't assign a new number for the key value. Be sure, though, that you have different names for any fields that are not the same.

Then, as Eliot said, create your "switch_to_SOPF" subroutine. I wouldn't put it in html.pl, though. I would put them all into the db.cgi file so they are all together and available for all of your databases.

I'm not sure how you're going to get to the SOPF add routine. You could have a link in the orders html_record -- "create SOPF for this order" -- or something like that. Anyway, somehow there has to be an input of the key value for the order db.

In the html.pl file for SOPF, in sub html_record_form, after

my (%rec) = @_;

add

At that point, all of the data for the order and the dealer should be in the %rec hash and you can use your form fields normally.

I hope this is what you wanted.

JPD

J P Deni, You have almost got what I need however there is more to what appears on the face of it.
I have lot of Hic-Ups to cross.

Letme take up my first issue,
I have used your relational mod for Relating Dealer and Order Database. From the Dealer's perspective it works fine. He can login with his ID, View only own order and Place(add) order. So while Displaying the records, It shows Dealer's Complete Information and the summary of his orders as Links. So dealer can click any of the link and see complere details
->My First problem is that when ADMIN logs in, His Display goes Heywire, I mean each Dealer's Complete record is displayed alongwith The summary of all Orders

HOW TO GET OVER THIS?


Regards

Vijay

Do you have it set up so that the user's info automatically is displayed as soon as he logs in, or does he have to click a link?

Either way, you need to set up an "if" statement (or an "unless" statement) that exempts admin from having to see all the records. I'll be able to tell you more once I know how your html.pl file is set up.


JPD

Hi JP Deni,

I don't know where I am missing.

I think I should have probably Short/Long Mod Installed for Order database.
Order Database shows correct display for admin as well as Users but it displays Long Display.

Do you think that is the only problem ? Now That I already have my Order_html.pl working can I modify it to include Short/Long Mod Safely.



Regards

Vijay

I'm not sure what to tell you, since I'm not sure how things are set up.

After a user logs in, what does he need to do in order to see his record? Does it come up automatically as soon as he logs in or does he have to click a "View" link?

I don't think the short/long has anything to do with this.


JPD

Hi J P Deni

It is like this,

1) When a User Logs in he uses a "dealer" dababase
2) Since I have used relational mod, He Gets to see two things
a) His complete Membership Information such as Address, Tel, Fax, Credit Limit etc.
b) A Summary of orders he has placed (Linked to "Order" db)
c) By clicking on the link he gets to see his complete Order
3) I have created only one link on the footer called "Place an Order" which points to
MyDBMANURL/db.cgi?db=po&add_form=1
I read your comment in this forum and got the clue(2 db's same ID & Passwd)
4) So once the user clicks on this link he can place the orders and he switches to "Order" db
5) On Order Page I am once again giving a link to "Place an Order" only and nothing else.
6) For the ADMIN ONLY, I want to give "view" or "List al"l permission on Orders Page.
Purpose is that admin should view all the Orders.
I am able to View all the orders but Only Full Description
I WANT TO SEE SHORT DESCRIPTION (Listing) so that it can be clicked and seen as full order.

Does It Zero down to Short/Long Mod For "Order" db? I am not very sure

Regards

Vijay

Yes, you probably do want to use the short/long display.


JPD

Yes ! I have added the Long/Short Mod and now things looks good.

I am still working on the relationship of "Dealer" and "Order" with "SOPF" Since I have one to one relationship in Orders and SOPF Can I have a Hyperlink on the "Order" Page which links me to the SOPF add routine ?
Only prblem is that SOPF is created by ADMIN class of user and they should not be part of dealer and Order DB's ( I hava different .pass file) so the login will have to be done again. will it remember the session as of which order page it came from ?

I am not too sure on how to start off with.

Help !

Regards

Vijay

Sure. You can have a link to the add form for SOPF.

In sub html_record_long, in the orders db, add

(change the text in italics above as needed)

As long as you have the same auth directory and same .pass file for all of your databases, everyone should be able to go from one to the other without any problem.

The only issue that might come up is that your non-admin users obviously have "add" permission for the other databases, but not for the SOPF database. You can overcome that by adding the following to the beginning of each of the subroutines in the html.pl file for the SOPF db:


JPD

I Had been working on my project of Order Processing System. I have not yet reached a success. Let me explain all of you once again what I am looking for, What I have done so far and what is Pending.

I am writing an Sales Order Processing system for a PC Distributor.
1) This is a B2B Application
2) I have dealer as registered members --- First database dealer
3) dealer posts po (Purchase Orders) for PC's --- Second database po
4) One dealer Places multiple orders - One to many relationship in dealer & po
5) Set Of Admin class users depending on region create Order Processing Form Document opf ---- Third database
6) There is one opf for every po - One to One relationship in opf & po

I have achieved so far
1) Have dealer database
2) Have po database
3) One to many relationship in dealer & po

I am struck on the following
1) After co-relating dealer & po how to generate a link for adding a opf database where I use inputs from both these databases.
2) I had a clue from J P Deni but I somehow could not Implement it .

My DBMan files are avilable at the following URL (all in txt format)

http://www.thakralindia.com/dbman/?

Filenames:
main script: db.txt
dealer Database: dealer.txt (cfg file), dealer_html.txt
po database: po.txt (cfg file), po_html.txt
opf database: opf.txt(cfg file), opf_html.pl

Thanks in advance for any help or clues.


Regards

Vijay

Trying to get attention from possible help

BTW, Anyone knows where is J P Deni & Eliot These days .....

Waiting.......

Regards

Vijay

miheer:

I sent you a private message which might help you. Try the changes I suggest and let me know if they help.

If Lois's private message doesn't do the trick, let me know and I'll see if I can come with anything else for you.


JPD

Thanks Loisc !

Your email looks very comprehensive !

I will give it a try and come back to you

It Is nice to See JPD back in action

Many Thanks to both of you



Regards

Vijay

Hello JPD and LoisC,

I am sorry to sat but I could not implement the changes suggested by you. I know I am almost close to It but Still nowhere as far as 3 way relationship is concerned.

JPD had correctly understood my requirements. My problem now is as follows;

When I click on the Link "Create OPF for this Purchase Order" it does not take any reference from the either po database nor dealer database. I am really struck on this single point

I have already posted all my required files at the following location
http://www.thakralindia.com/dbman/ ( all files in .txt )

Your help will be really greatful !




Regards

Vijay

First, let's try changing the link to create the OPF.

See if that does you any better.



JPD

Hi J P Deni,

Yes the link Looks fine. It is like as follows;

http://www.thakralindia.com/db2/db.cgi?db=opf&uid=admin.95987370577400&add_form=1&PO_Number=Wendy-001

Where PO_Number is the Key field and apperently it Selects and creates the Correct link.

Now My Problem is
How do I input Fields from my dealer and po database
Is it something like $rec2{'Dealer_Code'} .... $rec2{Company'} & $rec3{'PO_Number'} .... $rec3{'PO_Value'}

OR
Is it something like $rec3{'Dealer_Code'} .... $rec3{Company'} & $rec2{'PO_Number'} .... $rec2{'PO_Value'}

OR

Something else

Also, DO I have to keep $db_key_track=0 or 1 , I keep getting "Duplicate Serial Number Error"

Please Excuse me for my poor knowledge of PERL

I hope you will help me get over this problem




Regards

Vijay

I need to handle one thing at a time.

If I understood you correctly, the OPF records are not added by the user, but by someone else. Is this correct? If so, you need to change the $auth_user_field setting. It should be

$auth_user_field = -1;

The first thing you need to do is to set the correct key value for the new OPF record. In sub html_record_form, after

my (%rec) = @_;

add

$rec{$db_key} = $in{$db_key};

Then you need to get the information from the matching record in the PO db file:

At this point, let's make sure you're getting what you should get. After the above lines, add

See if that prints out the values from the PO for the OPF that you're trying to add.


JPD

Hi JP Deni,

I am following your instructions step by step.

1) Yes, you have understood corrctly, OPF is never added by user who adds PO ( Actually It is an Administrative function)

2) As per your advice, I have done following changes in opf.cfg
$auth_user_field = -1;

3) I also made changes to sub_html_record_form and now it looks like as follows;

my (%rec) = @_;
$rec{$db_key} = $in{$db_key};
&switch_to_po;
%rec2 = &get_record($rec{$db_key});
foreach $col (@db_cols) {
print "$rec2{$col}<BR>";
}

But, It does not print The values from the PO I am trying to generate OPF from
It leaves this space BLANK

I need your further guidence


Regards

Vijay

It's a good thing I didn't go any further.

Let's try this:

You should get

--PO_Number--
--the po number for the current purchase order--

If that works, change the code to

You should get

--the po number for the current purchase order--

If that works, change the code to

You should get a listing of all of the fields in the po database, one per line.

Let me know what happens.


JPD

Hi J P Deni,

All the Steps abovementioned happen exactly as you have explained. (Or Rather Expected )
Now I await for yourr further direction.

============================================
PS: Out of Curiousity, I tried to remove those Comments(#) in the last three lines. I get the listing of the fileds but I also get those many blank lines.


Regards

Vijay