Portal with dynamic child items?

[nangko]

Hi,

 

I'm building a database wich contains customers anditems (or services). In this database i store a sales price per item. This is the standard sales price used for customers. But for some customers, a different price than the standard price for a specific item is used. I also wan't to manage/store these customer specific prices for items in the database I'm building.

 

For this I'm using the following data structure:

Trhee tables: "tbl_customers", "tbl_items", "tbl_customeritems".

 

tbl_customers

_pk_id (Primarykey / UniqueIdentifier)

name (customername)

 

tbl_items

_pk_id (Primarykey / UniqueIdentifier)

description (itemdescription)

price (Price of an item)

 

tbl_customeritems

_pk_id (Primarykey / UniqueIdentifier)

_fk_customer_id (Foreignkey points to tbl_customers)

_fk_item_id (Foreignkey points to tbl_items)

price (Price of an item for a specific customer)

 

So every record in tbl_customeritems has a parent record in tbl_customer and a parent record in tbl_items.

 

Now I wan't to create a portal in a customer layout(displays records from tbl_customers). This portal has to show all item records from tbl_items (_pk_id, description, price) and (if present) the customer specific price from the in the layout selected customer.

 

How do I do this? I can't get it right.

I tried to create a calculation field "x_selected_customer_id" in tbl_items, and let tbl_customeritems:: _fk_customer_id point to this. But x_selected_customer_id won't update with the selected customer_id .

 

I really need to implement this for the database system I'm building. If this isn't possible I have to witch to a webbased solution (because I know I can build this in PHP/MySQL).

 

Thank's in advance for your help!

[LingoJango]

I'm thinking of something like a global field in tbl_items, used to link it to a (separate table occurrence of) customeritems. Relationship: tbl_items::customeridglobal = tbl_customeritems::_fk_customer_id AND tbl_items::pk_id = tbl_customeritems::_fk_item_id.

 

You set the global field to the current record's ID tbl_customers::pk_id, and then have a calc field in item that shows = Case ( tbl_customeritems::price ; tbl_customeritems::price ; tbl_items::price ).

 

Ought to work, I think; but you can't make the global field a global CALC field because it won't update properly while you're in the customer table.

[LingoJango]

BTW, I'm not sticking out my tongue at you, it's just the semicolon plus p effect. Disable smileys to view message properly.

[nangko]

Hi!

 

Sorry for the late reply, but thanks for you're advice.

I dropped fixing this problem and wend on with developing the onther functions of the system I'm builiding.

 

I will try your solution when I'm ready with the other parts of the system and will post the solution here if I found it

 

Thanks for the tips!

[Molson]

Now I wan't to create a portal in a customer layout(displays records from tbl_customers). This portal has to show all item records from tbl_items (_pk_id, description, price) and (if present) the customer specific price from the in the layout selected customer.

 

How do I do this? I can't get it right.

I really need to implement this for the database system I'm building. If this isn't possible I have to switch to a webbased solution (because I know I can build this in PHP/MySQL).

 

Hi Nangko,

 

.... I'd rather fight than switch from FM.... so in the interest of keeping you on the •right• track.... smiley-wink

 

The problem arises because the buylist for the customer does not include ALL the items for sale, only those items that have special pricing for the customer.

 

You need to create an additional set of table occurrences for this and I would recommend adding a global custID field to items. When you navigate to a customer, use a script that sets the value in the global and thus completes the relationship.

 

You now have Cust ---- Items, all related in the standard way: cust ID in cust matches cust ID in buylist; item ID in buylist matches item ID in items.

 

This first TOG is used to create the buy list for the customer and to see what items may be on the buy list, it won't work for the display you want. You need a complex relationship, so add the following to the graph and field to the tables:

 

Add the global cust ID field to Items (gcust ID).

 

In the relationship graph, add another TO for Items[2] and link it to customer using the Cartesian join (X) option to match cust ID to item ID. (Yes, I know those ID's don't really match, but the X join changes that. They are the two fields in both tables that you KNOW will have an entry, so match them.) This means all customer records will match all item records, because you want to display the portal showing ALL items. Use this relationship's portal on the customer layout.

 

Add another TO for BuyList[2] and link it to Items [2]: item ID = item ID AND add a second condition, gcustID (in Items[2]) = custID (in BuyList[2]).

 

Control navigation to your customer layout with a script that sets the gcustID in Items as the record is viewed. This completes the relationship and the field custPrice from the BuyList[2] can display the customer pricing for the item beside the standard pricing for the item.

 

(Please note, I am not recommending the naming convention used above for your second set of tables; they are just useful for the explanation. Use names that are meaningful to you.)

 

HTH,

 

Michele

[nangko]

Thanks for your help.

I' will try this as soon as possible.

 

Control navigation to your customer layout with a script that sets the gcustID in Items as the record is viewed. This completes the relationship and the field custPrice from the BuyList[2] can display the customer pricing for the item beside the standard pricing for the item.

 

Is there a way to trigger a script everytime a new record is beeing viewed?

 

Or do I have to make buttons (next/prev record) for the user to navigate through the record and hide the status area (scrolling through the records, doing searches with opperators will than not be possible).

[Molson]

Is there a way to trigger a script everytime a new record is beeing viewed?

.

You could use a free plug-in such as zippScript by John Kornhaus . Setting up calling the plug-in is a little on the advanced side.

 

 

Or do I have to make buttons (next/prev record) for the user to navigate through the record and hide the status area

 

I would recommend this. It depends on where you are in the development process and who the customer is. I never leave the status area visible in my solutions. I always control the user experience. The only exception to the status area visibility occurs when they preview a layout that is to print.

 

It just depends on how professional the finished product must be and how fool-proof you need it to be.

 

 

(scrolling through the records, doing searches with opperators will than not be possible).

 

You can still search using the operators, you just need to provide the user with feedback about the operators. I usually have a *find* screen that displays a replica of the operators. You could also allow the status area to be visible during the find process.

 

I would always remove all layouts from the layout menu, if you do allow the status area. This prevents the user from going to a layout that is not appropriate to the action they may be taking.

 

It's all a matter of how tightly you need to control the interface.

 

HTH,

 

Michele

[nangko]

You could use a free plug-in such as zippScript by John Kornhaus . Setting up calling the plug-in is a little on the advanced side.

I think I'll use this. Will definitely take a look at it.

 

I never leave the status area visible in my solutions. I always control the user experience. The only exception to the status area visibility occurs when they preview a layout that is to print.

I totaly agree, but this customer used to build very simple db apps in FM himselve and he cannot live without the little scrollbar in the status bar..

And word for word translated from dutch we say: "Customer is King" smiley-laughing .

So if he really wants it he gets it.