1 2 Previous Next 15 Replies Latest reply on Apr 20, 2009 7:51 AM by donjuancarlos_1

# Using values from portal lines in a calculation

### Title

Using values from portal lines in a calculation

### Post

I have a large portal on a Quoting daqtabse where some lines are calculated and some are entered by the user.  One of my calculation fields will be a total of values from portal rows 1,2, 4 and 6.  How do I reference these portal rows in a calculation?

• ###### 1. Re: Using values from portal lines in a calculation

Had this problem too.  Try:

GetValue ( List ( Table::Field) ; portal row number )

where portal row number is the number of the row you want.

So to do what you are looking for if I understand it:

GetValue ( List ( Table::Field) ; 1 ) + GetValue ( List ( Table::Field) ; 2 ) + GetValue ( List ( Table::Field) ; 4 ) + GetValue ( List ( Table::Field) ; 6 )

There is a potential problem with this if the field you are adding is empty in any of the rows before the one you are looking for.

List gives a list of the non empty values of the selected field.  In my case all fields we need this for always have a value.

There may be other ways.  Hope this helps.

-Erich

• ###### 2. Re: Using values from portal lines in a calculation

You can use the GetNthRecord() function to get specific related values directly, instead of going on a detour via GetValue ( List () ).

However, this is not a good approach overall. If there is an attribute that distinguishes some related records from others, you should use that, and not depend on the order of related records.

• ###### 3. Re: Using values from portal lines in a calculation

In your example, what makes the records in rows 1, 2, 4 and 6 different from the record in row 5?

Let's say there's a field, PortalTable::Selected, that contains the text "selected" in all the records but number 5 from your example.

In that case, you could define a calculated key field:

PortalTable::Key = If(selected = "selected", PortalTable::ID, "" )

Where PortalTable::ID is the field linking your portal records to the record in your main table.

Now you can create a relationship that uses the calculated key field to link your main table to just the records that you need for your calculation and you can use this new selective relationship in your calculations.

• ###### 4. Re: Using values from portal lines in a calculation

And another option that doesn't require an extra relationship:

If(table::Selected = "Selected", table::datafield, "" ) /* datafield is the field you need in your calculations */

with this approach, aggregate functions like Sum(), Count(), Average()

can be used with the existing relationship instead of specifying a new relationship.

• ###### 5. Re: Using values from portal lines in a calculation
There's nothing in my database that distiguishes these line items from the many others I have. I do have (thanks to PhilModJunk's suggestion), an ItemSequenceNumber field in my Line Items database, which I use to put the items in order in the portal.  I figure because the line order is already locked down I could use the GetValue method (which works, btw :) ).  But if I was was going to be cleaner and access the line items directly, I would have a calculation that adds the quantities from the Line Items database where QuoteNum=XXXX and the ItemSequenceNumber =1,2,4, and 6.  What's the calculation to do that?
• ###### 6. Re: Using values from portal lines in a calculation

I don't get it.

If "There's nothing in my database that distiguishes these line items from the many others I have", then how do you know to use items 1, 2, 4, and 6 but not 5? Obviously, there's something different about row 5 or you wouldn't have to exclude it from your calculation right?

• ###### 7. Re: Using values from portal lines in a calculation
I 'm building a form for entering quotes.  The line items are ordered in a way that makes most sense to someone who is quoting a building.  The first bunch of lines are sheet metal (classified by use and possibly type) and the lines 1,2, 4, and 6 are used to calculate how many screws and other hardware bits are required for the building.  I may not be on the same page yet.
• ###### 8. Re: Using values from portal lines in a calculation
To paraphrase back, It's always rows 1, 2, 4 and 6, never 5, 7, 8 or ... OK now I get the problem. What kind of calculation are you doing? a total, average, other...?
• ###### 9. Re: Using values from portal lines in a calculation
Total
• ###### 10. Re: Using values from portal lines in a calculation

Here's a simple approach:

Define a calculation field, "ComputedValue":

If(FilterValues(list(1; 2; 4; 6) ;LineItems::ItemSequenceNumber) <> "", LineItems::NumberField, "" )

Now the expression Sum(portaltableoccurrence::ComputedValue) will return the desired total.

• ###### 11. Re: Using values from portal lines in a calculation
Thanks!
• ###### 12. Re: Using values from portal lines in a calculation

While my simple solution works, you may want to implement a more generalized approach so you don't have to redifine your calculation everytime you add or remove a specific portal row from your calculation.

Define a field "Include" in your catalog (I'm remembering your earlier thread). Put a matching field in LineItems and set it to look up a value from the matching field just like you look up a unit price. Add the text "Include" or any other value you prefer to all the items in catalog that you want in your total.

Now use If(lineItems::Include = "Include", lineitems::numberfield,"" )