FM10adv - WinXP

I have a vendors table with a field indicating which line(s) of business
they support (A and/or P).

Acct# - LOB
vendor1 - A
vendor2 - AP
vendor3 - P

I want to present a page to the managers of those vendors and limit what the
manage sees depending on which LOB he manages.

Acct# - LOB
mngr1 - A (should see vendors 1 & 2 only)
mngr2 - P (should see 2 & 3 only)
mngr3 - AP (should see all vendors)

When the mngr logs in I set a global _LOB to the exploded key for all
possible combinations for those he manages seperated by pilcrows (ex- mngr1
sets to A AP PA : mngr3 to A AP P PA). I use this global as the relationship
into vendors (named VenorsICanSee).

This works great to filter records I can see in the portal and I thought I
was done. However I have a related records only value list based on the same
relationship which would allow let the mngr to select a different vendor to
review but it lists all vendors for all managers.

So close... what am I missing?

Oops - forgot to describe the setup of the value list (VL is named
ChooseVendor). The idea is to display the vendor's name which is in the
"Contacts" table but record the vendor's acct#. Contacts is tied to
VendorsICanSee via the VendorAcct#. This relationship is called
ContactsViaVendorsICanSee.

In setting up the VL I select "Use values from field" then specify "Use
values from first field" = "VendorsICanSee:VendorAcct#". Then I select "Also
display values from second field" which is set to
"ContactsViaVendorsICanSee:ContactFullName". Then I enable "Show only values
from second field" and "Include only related records starting
from:ContactsViaVendorsICanSee".

Hope this is clear enough...

"GSP@DavClaServ" <info (AT) davclaserv (DOT) com> wrote

GSP@DavClaServ wrote:
minor point in a way, but I would suggest the LOB should be a pilcrow
separated list (i.e a multiline key) or at leasts have an aecalc or
calc that processes it as such. that way, 'combinations' don't matter;
actually as you have it it is permutations , the order matters.

If the order doesn't matter, it is a Combination.
If the order does matter it is a Permutation.

So then the global left key would have three possible non null values
1:
A

2:
A
P

3:
P

GSP@DavClaServ wrote:
I would go about it this way:

create a key using List( VendorsICanSee:VendorAcct#)

point of order here, special characters (#) in field names are not sound
practice. The vendor id should be the target field (vendor_id)

So left multiline key c_manager_vendors = List(VendorsICanSee::vendor_id)

rel fom that to vendors::vendor_id
value list based on that rel, starting from ContactsViaVendorsICanSee

Attach the VL to the vendor_id field as a VL and the value you SEE will
be the vendor name but the value ENTERED will be the vendor id

If you want the (redundant) vendor name hard ci=oded i( in similar
situations) a second vendor name field can be added in the parent table
as a lookup, or script triggered entry...

Thank you for your response but I'm not sure I understand your suggestion.
(btw - the # in my post was for brevity. I do not use it in the database
design).

You say to create a (multiline) key using the list function. (Thanks for
pointing me to that function. I never used it before.) I can see where this
would return just the desired vendor account numbers which I would then
place into a field called c_manager_vendors. Which table would this field be
in? Would this be a global field? Should I do this with a script step or use
a calculated field?

Then I would set a new relationship from ??? table to the vendors table
where ???::c_manager_vendors = vendors::vendor_id.

I think the rest woud make sense after I get this much done.
thanks again

As noted in my original post below the LOB is separated by pilcows. "...set
a global _LOB to the exploded key for all possible combinations for those he
manages seperated by pilcrows..."

I'm not sure what an 'aecalc' is, automatic entry?

I already figured out that I could reduce the number of entries in the
multiline key if I could assure that the vendorLOB was always alphabetized.
For instance in my example below, vendor2 could wind up being either AP or
PA depending on the order in which the operator clicked the checkboxes on
the setup screen. (also keep in mind that my example shows only two LOBs
when in actually this could be any numer depending on the business rules) I
tried finding an "Alphabetize" function that I could call during the save
script that would insure the LOB field in the vendor table was set
alphabetically to AP but was unsuccessful. If I could accomplish this then I
would only need to have the A AP and P (combinations - pilcrow separated of
course) in my multiline key and would not need to have the PA (permutation)
in it.

Without a way to do the alphabetizing then I don't see how to avoid the
permutations.

"105" <cortical (AT) internode (DOT) on.net> wrote

On 23/12/09 3:40 AM, GSteven wrote:
ok


a calc unstored so it refreshes
it does not need to be a global calc
as for which table, when the manager logs in, what is the layout table
they are working on? Probably managers; it should be accessible to this
table

There are 2 ways here too. I use a data separation model by default, and
except in rare circumstances, create all such calcs in a 'globals'
table (aUser) in the user file. So the manager working layout could
either be based on the manager table TOC, or it could be based on the
globals table.

The simplest case is to use the manager TOC for the working layout, and
put the c_manager_vendors in managers

the next variation is to base the working manager layout on the globals
table. The manager id is parsed into a g_manager_id in aUser (the
globals table; has to be to establish the relevant manager filtered
relationships to data tables). The c_manager_vendors calc is in the
aUser table


the third variation would be to use a tunneled rel
- The working manager layout based on the manager table
- establish aUser::g_manager_id
- rel aUser to vendors
- calc key c_manager_vendors in aUser
- rel from manager to aSuer using a constant key pair c_1::c_1); or (x)
- tunneled rel manager (c_1::c_1) aUser (g_manager_id::manager_id) vendor



if your working manager layout is based on manager table, then the
simplest is calc c_manager_vendors in manager, rel from manager to
vendors (c_manager_vendors::vendor_id)




Here may be a good example of where a normalized structure may be more
flexible.
Rather than using LOB field in Vendors, a lineItems table VendorLOB
would be normalised.
The rel for the calc c_manager_vendors would then point to the VendorLOB
table.
This makes a range of stats and data mining (performance...) a whole lot
less convoluted downstream.
Arguably, one could/should normalize the ManagerLOB similary...

On 23/12/09 3:40 AM, GSteven wrote:
ok


a calc unstored so it refreshes
it does not need to be a global calc
as for which table, when the manager logs in, what is the layout table
they are working on? Probably managers; it should be accessible to this
table

There are 2 ways here too. I use a data separation model by default, and
except in rare circumstances, create all such calcs in a 'globals'
table (aUser) in the user file. So the manager working layout could
either be based on the manager table TOC, or it could be based on the
globals table.

The simplest case is to use the manager TOC for the working layout, and
put the c_manager_vendors in managers

the next variation is to base the working manager layout on the globals
table. The manager id is parsed into a g_manager_id in aUser (the
globals table; has to be to establish the relevant manager filtered
relationships to data tables). The c_manager_vendors calc is in the
aUser table


the third variation would be to use a tunneled rel
- The working manager layout based on the manager table
- establish aUser::g_manager_id
- rel aUser to vendors
- calc key c_manager_vendors in aUser
- rel from manager to aSuer using a constant key pair c_1::c_1); or (x)
- tunneled rel manager (c_1::c_1) aUser (g_manager_id::manager_id) vendor



if your working manager layout is based on manager table, then the
simplest is calc c_manager_vendors in manager, rel from manager to
vendors (c_manager_vendors::vendor_id)




Here may be a good example of where a normalized structure may be more
flexible.
Rather than using LOB field in Vendors, a lineItems table VendorLOB
would be normalised.
The rel for the calc c_manager_vendors would then point to the VendorLOB
table.
This makes a range of stats and data mining (performance...) a whole lot
less convoluted downstream.
Arguably, one could/should normalize the ManagerLOB similary...

On 23/12/09 5:18 AM, GSteven wrote:
auto enter calc aec_field

multiline key to multiline key; permutation don't matter
the rel from manager::LOB to vendors::LOB can be displayed as a portal
to list the manager vendors
the calc c_manager_vendors returns a list of the LOB filtered vendors
the value list is defined to show only the vendor names, using the rel
the global on the manager layout used to select a vendor, is atatched
toa g_vendor_id field. Here is the trick. although the field is
g_vendor_id, if the atatched vl is defined as a pop up MENU (not a ddl)
then the pop list will display the vendor names, and the global field
g_vendor_id will contain the vendor id, but display the related vendor
name (after exiting the PUM)
the g_vendor_id can then be used for a second rel to vendors...

have set an example file back channel