Thanks for the assistance with this; I find that my original select clause can be made to work, but I get syntax errors when using it the way I wanted to. I will spend some more time trying to sort this out.
Regards, John Docherty -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David M. Blocker Sent: Monday, 20 September 2004 3:07 a.m. To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Matching Values John Here's one possible solution. I'm sure there are others 1. Make sure your SCHEDULE table has a primary key column. 2. Create a form expression setting a variable to the value of that column: fKey = KeyColumn 3. Define a TABLE level EEP (in the menu on the forms screen: Tables / Add Remove Tables / Table Settings button / Entry/Exit Procedures: After Saving Row) which would: Use your SELECT to find the matching row in the RATES table: Sel rseq INTO eRSeq IND eI1 FROM rates where &al Use another SELECT to pull out the values of the other columns you want to get from RATES: SELECT col1, col2, col3 INTO eCol1 IND eI2, eCol2 IND eI3, eCol3 IND eI4 FROM RATES WHERE rSeq = .eRSeq Update the SEQUENCE table, row just saved, with these values: UPDATE SEQUENCE SET SeqCol1 = .eCol1, SeqCol2 = .eCol2, SeqCol3 = .eCol3 WHERE KeyCOlumn = .fKey Be sure to include error traps for: 1. No row found in RATES 2. UPDATE fails for some reason David Blocker [EMAIL PROTECTED] 781-784-1919 Fax: 781-784-1860 Cell: 339-206-0261 ----- Original Message ----- From: "John Docherty" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[EMAIL PROTECTED]> Sent: Sunday, September 19, 2004 6:35 AM Subject: [RBG7-L] - Re: Matching Values Alastair, Thanks for your reply. To explain further, there is no link between the two tables as such. The aim is to allow the entry of a row in the Schedule table, via a form, with a description, de, that can vary a little, and then to use an EEP to determine the matching item in the Rates table, based on the predefined select clause which is also a column, al, in the Rates table. (The select clause 'analyses' the description from the Schedule table to select a unique row in the Rates table that matches the criteria used to define the item.) The appropriate labour and material rates would then be selected from that row in the Rates table, and entered into the row in the Schedule table. Hence the EEP needs to contain a select clause that finds the row in the rates table where the select clause in column al is true. Something like Sel rseq from rates where &al is true, would choose the correct row, but how does one actually achieve this ? The alternative I suppose would be to limit the description entered so that it was actually selected from the rates table via a list. Maybe this would be the simplest solution. (The original reason for setting up the select clause as a column was that we receive schedules from various consultants who differ in the way they describe the same item. The solution I have been using with the cursor works when analysing a schedule that has already been set up - what I now want to do is to allow this to be done dynamically when we do our own schedules, without requiring the descriptions to be too rigid.) I hope that this explains what I want to achieve. Any further thoughts would be appreciated. Thank you. Regards, John Docherty -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alastair Burr Sent: Sunday, September 19, 2004 6:43 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Matching Values John, it's usually a bit quiet on this list over the weekend and I'm not at all sure that I follow what you're trying to do. However, here's a couple of suggestions that you can throw back if you want with some more detail if you can: Firstly, it sounds as if you can use the same "where and &val" clause to select the row that you want to edit as you do at the moment. If so, you can use this to display the row on your form based on the Rates table in a DBEdit based on the Schedules table. However, it sounds as if you don't have a simple link between your tables so this could be a problem. Alternatively, and especially if a link between the tables is your problem, you can use the "where and &val" clause in your EEP to pop-up a second form based on the Schedules table: edit using [new form] ... where and &val If the row doesn't already exist in the Schedules table then I would create it in your EEP first and pop-up a second form based on the new row to edit. Regards, Alastair. ----- Original Message ----- From: "John Docherty" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[EMAIL PROTECTED]> Sent: Saturday, September 18, 2004 8:47 PM Subject: [RBG7-L] - Matching Values I have an estimating package which has a RATES table and a SCHEDULE table. The RATES table includes an item description (column de) and, as the description for the same item can vary depending on the consultant preparing the schedule, another column which includes the basis of a select clause used to identify the item (column al). Each row in the RATES table has a unique number identified by column rseq. eg: de: 400 dia spigot with damper al: (de contains '400' and de contains 'spigot') rseq: 100 While using a cursor to step through the RATES table, and an update clause, I can extract the appropriate rates and update the SCHEDULE table (update schedule set .... where and &val; val being the variable extracted from the al column using the fetch statement) I am trying to develop this package to allow manual entry of the data into the SCHEDULE table (so far this is imported from a spreadsheet supplied). As part of this I need to determine a way of doing this from an EEP in a form as soon as the description is entered into a form. This would require a command which selects a row in the RATES table where a select clause returns a match between the description in the SCHEDULE table (which would be a variable from the form) and the predefined select clause stored in the RATES table. Select rseq from RATES where &al is true If anyone has any suggestions as to how this can be achieved I would appreciate your assistance. (Maybe I have just missed the obvious here.) Thank you, John Docherty -- Outgoing mail is certified Virus Free. Checked by AVG Anti-Virus (http://www.grisoft.com). Version: 7.0.271 / Virus Database: 264.9.3 - Release Date: 17/09/2004 -- Incoming mail is certified Virus Free. Checked by AVG Anti-Virus (http://www.grisoft.com). Version: 7.0.271 / Virus Database: 264.9.3 - Release Date: 17/09/2004 -- Outgoing mail is certified Virus Free. Checked by AVG Anti-Virus (http://www.grisoft.com). Version: 7.0.271 / Virus Database: 264.9.3 - Release Date: 17/09/2004 -- Incoming mail is certified Virus Free. Checked by AVG Anti-Virus (http://www.grisoft.com). Version: 7.0.271 / Virus Database: 264.9.3 - Release Date: 17/09/2004
