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 F0: ITEMASA 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 inTuator. Regards, -Greg Gregory Novak Manager North American Technical Services Momentum Utilities Pty Ltd (630) 985-1736 gnovak@xxxxxxxxxx www.jacana.com Making Information Work for your Business www.intuator.com -----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? TIA Jim _______________________________________________ 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, visit: http://lists.midrange.com/mailman/listinfo/mapics-l or email: MAPICS-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/mapics-l.
As an Amazon Associate we earn from qualifying purchases.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.