Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Calculated lookups based on dropdown selection


visuallogic
 Share

Recommended Posts

Hello,

 

I'm trying without success to do the following...

 

I have a portal with a Artist table as context. I have related another ArtLabourItems table in another file to the Artist table.

 

Then I have a ArtLabourType dropdown field in the portal that defines an automatic value list from the ArtLabourType field in the ArtLabourItems table. This works fine.

 

There is also a Price field in the portal that is defined as a calulation. When a ArtLabourType is selected from the dropdown I want the Price field to lookup its value via a calulation from the ArtLabourItems table.

 

Basically I just want to select a value in the dropdown and then have other portal fields automatically filled in.

 

Right now when I select the "One Hour" option from the dropdown I get a "?" in the portal Price field when using the following calculation for the Price field...

 

Case(

ArtworkLabourType = "One Hour"; Lookup (ArtworkLabourItems::Artw orkLabourBasePrice);

ArtworkLabourType = "Elaborate";"1000";

"0")

 

How do I get this to work?

 

Thom

Link to comment
Share on other sites

Try two fields instead of the single price field: LookupBasePrice, a number field defined as a lookup from ArtworkLabourItems; and ActualPrice, a numerical calc field, Case(ArtworkLabourType= "One Hour", LookupBasePrice, ArtworkLabourType="Elaborate", 1000, 0)

 

 

 

 

But if the lookup is based on ArtworkLabourType, and ArtworkLabourType is also the field such that in your calculation formula there's only one value it can contain ("One Hour") that would actually result in a lookup occurring and then being used, why is it a lookup? Or is the relationship to ArtworkLabourItems based on the values of ArtworkLabourType in conjunction with some other fields? Is there more than one price over in ArtworkLabourItems associated with "One Hour"?

Link to comment
Share on other sites

Yes, the lookup is based on the single value the the ArtworkLabourType dropdown field can have. What I would need is for multiple other fields in the portal row then to automatically get/lookup thier data from the record matching the value of the ArtworkLabourType dropdown field.

 

I'm not sure this can be done using calculating fields. Each of these other portal row field's would have to go to the external Products database file which just has products which are not related/matched to any field/table in the main database file. Then they would have to do a find for the record matching the value in ArtworkLabourType and the copy from the correct field in the found record and then paste this data.

 

This seems difficult to do using a calculation... or can a calculation use a script?

 

Thom

Link to comment
Share on other sites

At least one of us is lost, maybe both.

 

Would you be willing to temporarily suspend all ideas about how you're going to do something (calcs, fields, lookups, relationships, etc) and explain to me what you want to do here?

 

Is it different from this, or is this accurate?:

 

A record has a field for ArtworkLabourType. When you select the value from a dropdown list of appropriate values for ArtworkLabourType, the Price automatically fills in with the correct Price value.

Link to comment
Share on other sites

Yes, the following is accurate...

 

A record has a field for ArtworkLabourType. When you select the value from a dropdown list of appropriate values for ArtworkLabourType, the Price automatically fills in with the correct Price value.

 

Thom

Link to comment
Share on other sites

Then you don't need calcs. You need a numerical field in ArtworkLabourItems (the Price field) and it should be set to look up its value from a related table in which each named item (or Item ID) is given a price. Each possible value for ArtworkLabourType should appear in that other table exactly once.

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use