Basic Relational Design Question

[cbsteven]

I made a database which stores orders from our companys website. However this was also my first DB undertaking, and it is a totally non-relational design. I am now looking into making it relational and have made some test files to see how hard it will be.

Right now I am working on having a separate line items file and having the line items be displayed to the user in a portal from the main file. However, I have two scripts which need to operate on specific line items.

For example, in my current file I have room for ten line items, and next to each line item are two buttons, which point to 20 different scripts. There are only two different scripts but there are 10 copies of each with the specific fields they refer to changed.

For example, the first script is to display in a web browser window the specific item that is stored in that line item. So each line item has a hidden field called Item-URL which is a concatenation of the base url ("http://www.site.com/item?" & part_number_1), so the ten scripts basically just say "Open URL <Item-URL-1>" through "Open URL <Item-URL-10>"

Having 10 different short, very similar scripts for 10 different line items is cumbersome, but it works, and I am having trouble getting the same functionality with a relational design. I tried two methods:

1) Make a local calculation field which combines the text url with Line Items::Order Number. However if I do this it always shows the order number from the first matching field.

2) Make an Order URL field in the Line Items file which creates the URL text, and make a script which will go to the url. Then, in the main file, have a script which will execute the external Line Items::Go to Url script. Again, this only executes the script of the first matching record in Line Items.

Basically, I want to have a portal, and in each portal row have a button, and each button, when clicked, will open the proper URL of the Order Number field which is in the same portal row the button clicked is in.

[BobWeaver]

Two options:

1. Put your buttons right in the portal row with the line item. The buttons will repeat for every lineitem in the portal. You only need one script for each button, and when you click a button in a portal row, that row will automatically become THE related record for purposes of the script that runs.

2. Put your buttons (only 2 of them) outside the portal row. The user clicks on a portal row to select the line item, and then clicks one of the two buttons. Your script must then immediately capture the portal row in a global field with the following script step:

Set Field [gSelectedLineItem, Status(CurrentPortalRow)]

Once you have done this, you can then use the various other portal navigation script steps to do what you need to do.

I personally prefer method 1, but method 2 is also very popular.