The Mapics report writer Info/WorkPlace (IWP) can do exactly what you want.
A relationship can be built using the current date or field from a file
(such as order date) that will retrieve the most recent price record for an
item before the specified date.  

For those of you who know IWP, it does this by positioning within the price
file by item and date, then retrieving the prior record from the file.  This
is built into a subset on the file link keys.  Positioning is identified by
putting parentheses around the field name and setting the Link option
Matching Records to Retrieve to LAST (or for the next record, use FIRST).  

For example, in the Retrieval Map
F1: |- MBBZRES1   Keys: ITNBR, (DATEFLD)   Num Recs: 1:1

When Query leaves you wanting, IWP is just getting started...

Momentum is the developer of Info/WorkPlace, JacanaForms and Jacana


Gregory Novak
Manager North American Technical Services
Momentum Utilities Pty Ltd
(630) 985-1736
Making Information Work for your Business

-----Original Message-----
From: Ashworth, Thomas R. [mailto:TAshwort@xxxxxxxxxxxxxxxx] 
Sent: Wednesday, April 14, 2004 3:14 PM
To: MAPICS ERP System Discussion
Subject: RE: [SPAM - Header] - COM Pricelist - Email has different SMTP
TO:and MIME TO: fields in the email addresses

The secondary key to the Item Base Price file is BZBLDT - Price
Effective Date

There may be multiple base prices defined, but only one is valid for any
given date. Using Query, I don't know how you would 'build-in' the same
logic that applies in the application to give you a single value; LE /
GE will give [potentially] multiple values and it is very un-likely that
all items would have the same effective date (ruling out EQ). 

Thomas Ashworth
Business Applications Manager
Altec Lansing Technologies, Inc.

-----Original Message-----
From: mapics-l-bounces@xxxxxxxxxxxx
[mailto:mapics-l-bounces@xxxxxxxxxxxx] On Behalf Of James Staber II
Sent: Wednesday, April 14, 2004 3:50 PM
To: MAPICS-L@xxxxxxxxxxxx
Subject: [SPAM - Header] - COM Pricelist - Email has different SMTP TO:
and MIME TO: fields in the email addresses

I'm trying to build a parts pricelist in QRY, using MBBZREP/BZANVA (item
base price) as the price to be printed.

Query kinda works but I'm getting a line printed for each part # for
every time the part price was changed.

Is there any way to only print the current part price or am I looking
the wrong file/field?



This is the MAPICS ERP System Discussion (MAPICS-L) mailing list To post
a message email: MAPICS-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
or email: MAPICS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2021 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.