I think it's safe to agree that 30 seconds is toooo long.
I am trying to do lots of things here...
1) _c = grouping all my comments and then referencing the 1st (most recent comment) for each HotelId
2) Create a collection off the HotelRFP As _p data source that was 262 rows in it.
3) As I loop through each row I grab the most recent comment value for the specific HotelId, I grab the Status row in the RFP Lookup list for the a match on Status and also the row for the appropriate dictHotelBrand and Chain ...
4) Then lastly I patch to the collection numerous variables which in turn are also based on LookUp values from 4 other tables and the previously provided values in #1, #2 and #3.
The largest list is the main HotelRFP list with 262 rows. Suggestions for getting this down to 5 seconds runtime? I was looking in the Monitor and the last run had the ForAll taking 61,7956ms. The next closest row in monitor is 1,412 ms.
With(
{
_c: ForAll(
GroupBy(
RFPComments,
"HotelId",
"GroupedItems"
),
With(
{
_top: First(
Sort(
GroupedItems,
Modified,
Descending
)
)
},
{
HotelId: HotelId,
MostRecentComment: _top.Modified,
MostRecentCommentStatus: _top.Comment,
DateCreated: _top.Modified,
CommentWho: _top.'Modified By'.DisplayName,
StatusId: _top.StatusId,
CommentCount: CountRows(GroupedItems)
}
)
)
},
ClearCollect(
RFPListing,
ForAll(
HotelRFP As _p,
With(
{
_mrc: LookUp(
_c,
HotelId = _p.ID
)
},
With({
_col: LookUp(
RFPLookUp,
ID =_mrc.StatusId
),
_br: LookUp(dictHotelBrands,ID=_p.brandId),
_ch: LookUp(dictHotelChains,ID=_p.chainId)
},
Patch(
_p,
{
Status: _col.Title,
StateName: LookUp(dictStates,ID=_p.stateId,stateName),
CountryCode: LookUp(dictCountries,ID=_p.countryId,countryCode),
ContinentCode: LookUp(dictContinents,ID=_p.continentId,continentCode),
ChainCode: _ch.Title,
BrandCode: _br.Title,
ChainName: _ch.name,
BrandName: _br.name,
MetroCode: LookUp(ColMetrosUsed,ID=_p.metroId,metroCode),
ColorR: _col.ColorR,
ColorG: _col.ColorG,
ColorB: _col.ColorB,
MostRecentComment: _mrc.MostRecentComment,
DateCreated: _mrc.DateCreated,
CommentWho: _mrc.CommentWho,
CommentCount: _mrc.CommentCount
}
))
)
)
)
);
Thoughts...
