Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Working with multiple DB's in one view

Status
Not open for further replies.

elgemcdlf

Programmer
Oct 20, 2006
2
US
Here is what I am trying to accomplish and have been stumped so far. I ave set up a bill to db, ship to db and zip db. In a form view I have all the fields I desire from bill to on the left side and all the fields from ship to on the right. I wish to create a new record in both db's when using a single button to create new record.

If cust_num is in bill to I want the info placed in the fields accordingly. If cust_num is new I fill in bill to data. Many ship to's can go with a single bill to so I need the ability to fill in ship to info on the form regardless but if a duplicate to a record in ship to then just quietly delete the entry.

Now to the zip db. When finished entering the zip in either zip field I want Approach to search the zip db and locate the correct city and state and then fill the appropriate fields with the data.

Any help with this dliema would be greatly appreciated.
 
In Approach, you base a form view on ONE database and in your case it sounds like you are creating an order-type form, so your main database for the form should be the your bill database.

The bill database would be joined using its cust_num field to the corresponding field in bill-to. This would automatically cause the bill-to fields to appear when you enter a proper cust_num into the bill's field. If you set the join options properly to create a new record in bill-to if no records in bill-to match the cust_num in bill, then typing into the bill-to fields will create a new record in that database.

The ship-to is more complicated because you say you have multiples of these per customer. So you need a ship_num field in your bill database to let you select from existing records. I would set this up as a "field box and list" with the description being the address and you can filter it using the value in cust_num matching the cust_num field in the ship-to database so you only see matching shipping addresses.

Creating a new record in the ship-to would mean that you enter a new ship_num in the bill database (one that is not already assigned), then typing into the ship-to fields would create a new record if your join options are set up that way. You may want to use a macro attached to the ship_num field in the bill database to determine that a new shipping address is desired. This way you can fill in the cust_num field in the ship-to record.

In my applications I use a different method. I have the user first select the customer for the bill on a view based on the bill-to database, and view the customer's ship-to records in a repeating panel on the customer view. That way a new ship-to is created there, if needed, and then the ship-to record is selected for the order, which runs a macro to create the bill record with the cust_num and ship_num filled in. The macro switches to the form based on bill with the cust_num and ship_num stored in variable-type fields. Then it issues the RECORDS New and fills in the two fields in bill from the variables using SET commands.

To see a demo of one of my programs (one without multiple ship-to's) go to and download the trial version. I can add multiple ship-tos to this program if you like it otherwise.

Sue Sloan
 
You are quite close in concept I am building an order form but not in the sense of orders. What comes next in the process is similiar to an order form will be a repeating panel in which to enter qty, size, weight & type of shipment. From there the data is calculated to generate a shipping class and total weight then utilizing XML sent to all providers of either LTL or FTL (depending on needs of that order) and quote info piped back into a quotes db much like a line item of the orders db fielded from order num.

From there the quote selected will open the browser and send back relevant info to retrieve quote info and schedule a pickup.

I am creating a shipping db. The orders are completed and at this point the need is to quickly determine best method of shipment with the ability to retrieve info in the future should the need arise. Example being a discrepency in shipping bills. The order num is tied to cust num. I realize I can do this with multiple forms I am trying to make this as user friendly as possible without starting from scratch and writing it all in C :)

Thanks for your input and thoughts as to a direction to follow.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top