bazzer747

Hi I have a table with a field called 'cTaxYear', which is created as an Internal Calculated field (fkInternalCalc), it's a 7 character string field and contains, for instance, '2008-09', being a tax year (here in the UK) from 7Apr2008 to 6Apr2009. The calculation is based on the date a dividend is paid out and works out what tax year that dividend falls into. It works perfectly well in dbgrids etc. However, when I try to create a filter, for instance to filter all the records with a tax year of '2008-09' the dbgrid displays nothing. There are about 150 records which do contain '2008-09' in the 'cTaxYear' field and the filter expression is 'cTaxYear = ' + QuotedStr( '2008-09' ); Reading about filters I see it says 'Only the fkInternalCalc and fkAggregate fields can be used in filtering, sorting, or locating operations....' (which is why I created an fkInternalCalc field)) but says nothing more about any settings that should be used (like maybe setting indexfieldnames). Does anyone know for sure that an InternalCalc field can actually be used in a filter expression? Or if it definitely can't?

Hi I setup my small project and tried again, with no success. I tried with a normal Filter and also with OnFilterRecord, neither worked with an InternalCalc field. The documentation suggests it should work, but it doesn't (for me anyway). If you did get it working can you show some code showing the internalcalc field and the filter code. Maybe it's just a small mistake somewhere that stops it working. PS I have it all working fine now using a SELECT SQL statement, so IO'm just now interested in the final answer.

Hi, I see this but it doesn't help me in that it isn't working for me - I've listed the code I'm using above and there is nothing complicated about it. So if you can filter on an internally calculated field then why does it not work? Should work and actually working are two different things, as I've experienced in Delphi many times over the years. I'll set up a small project with nothing other than the bare minimum and try again with a more simple filter and see what happens. If that still doesn't work for me I'm going back to a simple Select statement to filter the records (which I know works as that's what I did before trying the internal calc way.

Lajos, Are you referring specifically to Internal Calculated fields here?

I'm misunderstanding a little here. I am using OnCalcFields AND it is working - I can see the result in a dbgrid. And the documentation indicates you can filter on an internal calculated field. I see what that link and you say about it but it doesn't ring true does it if the calculation is doing what it should do? And they give an example using it which seems to refute what they say. It must be a nightmare trying to keep the documentation up to date. However, It's looking like it's a no goer for me then. I'll use the old SELECT statement to filter the records. Pity Cary Jensen didn't note an issue in his in-depth book (I'll drop him a line, but no doubt he's inundated with questions about his book), it would've saved me some time, but hey-ho, you learn something every day. Thanks for your time helping me with this.

procedure Tdm.fdqDivsCalcFields(DataSet: TDataSet); begin //PART OF ... if ( fdqDivsDividendDate.AsDateTime > StrToDate('06/04/2018') ) AND ( fdqDivsDividendDate.AsDateTime < StrToDate('07/04/2019') ) then fdqDivscTaxYear.AsString:= '2018-19'; if ( fdqDivsDividendDate.AsDateTime > StrToDate('06/04/2019') ) AND ( fdqDivsDividendDate.AsDateTime < StrToDate('07/04/2020') ) then fdqDivscTaxYear.AsString:= '2019-20'; if ( fdqDivsDividendDate.AsDateTime > StrToDate('06/04/2020') ) AND ( fdqDivsDividendDate.AsDateTime < StrToDate('07/04/2021') ) then fdqDivscTaxYear.AsString:= '2020-21'; END; And in the click on a radio group: with dm.fdqDivs do begin Filtered:= False; if (cAll = 'ALL') AND (cTaxYear = 'ALL') then Exit; //No filter required if (cAll <> 'ALL') AND (cTaxYear = 'ALL') then Filter:= 'Whose = ' + QuotedStr( cWhose ); if (cAll = 'ALL') AND (cTaxYear <> 'ALL') then Filter:= 'DividendDate = ' + QuotedStr( cTaxYear ); if (cAll <> 'ALL') AND (cTaxYear <> 'ALL') then Filter:= 'Whose = ' + QuotedStr( cWhose ) + ' AND cTaxYear = ' + QuotedStr( cTaxYear ); Filtered:= True; end; cWhose is a letter value indicating a persons initial. cTaxYear holds the selected value to filter on (eg '2018-19').

Sorry, no. The original SQL query returns all the records, of which I want to filter just some of them. I can use a new SQL query to do this job (as I have done in the past) but I wanted to try using an internal calc field to see if this is quicker/more efficient (which I believe it is). All I want to know for certain is that I can filter on an internal calc field. If I can I'll dig a lot deeper with testing etc. to see why it isn't working. I would have thought a simple 'Filter = 'QuotedStr( 'cTaxYear' ); statement, where cTaxYear holds the relevant data would work, but it doesn't (and I've checked the values from this variable against the internal calculation results and they match.

Interesting. How would an InternalCalc field be calculated then? Yes, the calculation is done on the OnCalcField, and works fine displaying all the correct calculations (which I see in a dbgrid).

Hi I have. I've read the documentation on the Embarcadero wiki site, and I have Cary Jenson's book 'Delphi in Depth: Firedac', and quoted what it says above. It isn't in enough detail to say specifically you can filter on an internal calulated field, but it does say: 'Only the fkInternalCalc and fkAggregate fields can be used in filtering, sorting, or locating operations....' which says you can use filtering on an internal calculated field. But I'm trying it and it doesn't - but that maybe because there are some details or constraints required to do this which I can't find anywhere.

Thanks for the information, that's certainly one way to go. I have setup the internal calc field so am keen to understand if it really is possible to filter on that field (as the official documentation seems to suggest).

Oops, sorry. I'm using a MSSQL database.

Hi, I came across an oddity a few days ago in a report I had created some years ago (this was with Ace Reporter). This report had a jpg image which I wanted to replace with a png image. I deleted the image from the report then placed a TSctImageLabel (the equivalent of a TImage the reporter requires for inserting images). Problem was, whilst the 'Picture' property was there with the 3 dots ready for selection of an image, it was sort of greyed out, in that I couldn't click on the dots. I thought this might be an issue with the report component (and have put in a support request to them). However, today, on a different project, I had need to insert an image onto a form. Lo and behold, when I added a TImage to the form the 'Picture' property behaved the same way. I couldn't select an image. I've now also created a new VCL project and just added a TImage and can't select an image. I can load the image I want successfully from code with: Image1.Picture.LoadFromFile('D:\logo3.png'); - setting whatever properties I want from the object inspector. But I really need this image 'included' in the executable and not have to distribute it with the exe. Could this be a problem with my Delphi installation (I use 10.4.1 with all patches applied), or the TImage itself? Or have I messed up somewhere (even I think this is most likely! but I can't see how)

Got it! I have been systematically removing add-ons and testing the TImage 'Picture' property. In a recent webinar Embarcadero were giving a 'free' component from Dev Express - a VCL NavBar toolbar component. Whiuch I downloaded and tried, very nice it was. However, as soon as I removed the DevExpress component TImage worked OK. I've informed DevExpress, maybe Embarcadero should make sure any giveaways they do work correctly 🙂 Thanks for the suggestion Remy.

Often I'll trawl through the different forums and maybe open one or two of them, as my interest is peaked. Sometimes I don't see anything of interest. What I always do when I come out of that topic is to press the big icon which supposedly marks the forum as read. If I enter that forum then, sure enough, all the topics are marked read. However, when I come here on the following day and see there are 'new' topics, when I enter the forum the same questions I thought had been marked as read yesterday are not. It's only if I open the question and close it does it remain read. Or if I press the 'Mark forum as read' text to the right. But to do that I need to enter the forum first. It's annoying to see what I think is new material only to enter the forum to see it's questions I've previously decided not to open. Is this the way it is supposed to work?

Hi I'm struggling to enter a Null value into a field with a Firedac ExecSQL Update. My code to update a record looks like this: dm.fdc.ExecSQL( 'UPDATE tbCalendar SET EventDate = :pEd, Event = :pEv, EventTime = :pEt, Venue = :pVe, EventType = :pEvt, [Order] = :pOr, Competition = :pCo ' + ' WHERE ID = :pID', [ dDate, edEvent.Text, edTime.Text, cbVenue.Text, cbType.Text, StrToInt(cbOrder.Text), Null, iMatchID ]); 'dm.fdc' is the connection in a Data Module (dm). The 'Competition' field is not filled in by the User so needs to be assigned a Null value. Adding a new record does this by default but if the user has added a value and then wants to remove it I need to assign a Null back to the field. The above code has worked but at other times gives an error message: ..' data type is unknown... Specify TFDParam.DataType or assign TFDParam value before Prepare/Execute call.' But what DataType is Null and how does that code look? I've searched for how to do this but can't find a clear result. I've seen that you can assign Null to a field using 'Clear', but how does this get implemented. I've tried dm.fdcCalendar.Edit; dm.fdcCalendar.FieldByName('Competition').Clear; dm.fdcCalendar.Post; And that gives an error - '..update table does not have a PK or row identifier, record has been changed/deleted by another user' which is of little or no help as there is no other user as I'm testing. The Embarcadero wikipedia help is of little help, fofr some reason it refrains from giving code examples on most items I look up. And other sources, whilst nearly echoing what my issue is aren't quite the same issue.

Ah, Isn't this what Stano says - don't uuse direct assignment to a field because of possibility of SQL injection? Also, I did try the .Clear setting but it didn't work, as I mentioned earlier.. Thanks for responding..

Hi Yes, I agree. That's why I always use parameters in my ExecSQL statements. My problems with the ExecSQL is that I don't know how to assign a null value in the statement.

Yay. My code now looks like this: dm.fdc.ExecSQL( 'UPDATE tbCalendar SET EventDate = :pEd, Event = :pEv, EventTime = :pEt, Venue = :pVe, EventType = :pEvt, [Order] = :pOr WHERE ID = :pID', [ dDate, edEvent.Text, edTime.Text, cbVenue.Text, cbType.Text, StrToInt(cbOrder.Text), iMatchID ]); dm.fdqvCalendar.Edit; dm.fdqvCalendar.FieldByName('Competition').Value:= null; //To set the field to Null dm.fdqvCalendar.Post; And this works without any error. I've dropped the Competition field and parameter from the ExecSQL but other fields may well be updated, and then apply the Edit/Post afterwards and this works OK. So many thanks for the quick response. I suppose it would be 'cleaner' if I couldd apply a Null value withing the ExecSQL statement, but, ,hey, if it works I'm happy with that.

I used Delphi Professional for years, and had the Firedac add-on as together they gave me all I wanted from the product. I'm not into cross-platform development or server development so Enterprise was of no interest. Until they stopped the FireDac add-on in Profession a few years back. However, at the same time, they did a very good deal (so I thought!) to migrate to Enterprise, which includes Firedac. Which I did. Pity, I should have bitten the bullet then and used a different method of connecting databases, and stuck with Professional. All my projects used Firedac quite intensively so to move away from Enterprise now would be a major step. The cost of an Enterprise licence went up over 30% this year, completely unjustifiable in my opinion, and I've therefore lapsed my licence. 10.4.1 is where I am and where I'll stay for the foreseeable future.

Hi, I have a table with a column of numbers, which go from -6 to 54. This table is split into 3 divisions -6 to 16, 14-20, and 21 to 54.. I display the records in a dbGrid with a radiogroup (rgDivs) showing the 3 divisions, plus 'All'. When a user selects a division I use a SetRange to limit what is shown. The code in the radiogroup OnClick event looks like this: cChoice:= rgDivs.Items[ rgDivs.ItemIndex ] with dm.vEnrolments do begin if cChoice = 'All' then CancelRange else if cChoice = 'Div 1' then SetRange( [ -6 ],[ 16 ] ); if cChoice = 'Div 2' then SetRange( [ 14 ],[ 20 ] ); if cChoice = 'Div 3' then SetRange( [ 21 ],[ 54 ] ); end; dm.vEnrolments is a View of a table with all the records and cChoice is the value selected in the radiogroup. Clicking 'All', 'Div 2' and 'Div 3' does exactly what it is supposed to do, but clicking 'Div 1', which should show all records from -6 to 16 does not show the negative numbers. Any thoughts on why this should be would be appreciated.

What I've done now is created an internal calculated float field 'Exact2', converting the hyphen and Null values to 99, and set the IndexFieldNames to 'Exact2' and the SetRange now works correctly.

Hi Uwe, Darn, was hopeful there. 🙂 But just the same. However, it got me thinking deeper! An issue I have sometimes when a seemingly obvious problem might not actually be so 'clear cut'. The Field 'Exact' is used in a View made up from several tables, one of which owns the 'Exact' field. When I look at the design I can see the field is set as VarChar(4)! The field is 'supposed' to be (and should be) set as a float field as it shows golf handicaps that range from -6 to 54. I'm 'guessing' that the field was set as a varchar as some people don't have a handicap so the field is either NULL or has been set to '-'. I saw that just perusing all the values (about 100,000 of them) and then it dawned on me! I can see that this is why the SetRange doesn't work, as I've assumed (that dreadful word) the field is a number and it's actually a character. My solution is to (maybe) create a new float field which the SetRange will actually work on correctly. Sorry for this error on my part, but many thanks for steering me which caused me to find this out....

Hi, What I didn't show in my code snippet was this: dm.vEnrolments.IndexFieldNames:= 'Exact'; 'Exact' being the field the SetRange is acting upon. I did read Cary Jensen's FireDac book and took this idea of using SetRange setting the IndexFieldName to the field in question. Which works fine for all values from 0 upwards - it's just why does it not work on negative numbers that puzzles me.

FireDac query

Exotic? In what way? Is it known NOT to work in these instances?