Formula field not showing in crystal report 8.5

Question

I'm using an old version of crystal reports 8.5 and I'm having problems in showing a field in the details.

This report uses 6 tables in SQL Server database:  
2 in database1:
tblRecibo, tblCanaisDistribuicao

3 in database2: 
tblPessoas, tblMoradas, tblCodPostal, tblRamos

tblRamos.CodRamo is an int datatype and I want to display it in the details section.

@N_Apolice

left(CSTR({tblRamos.CodRamo}, "000"),3)

It seems that I am not able to get any field from this table, yet I can access fields from other tables of the same database in the Group-Header section (This report uses tables from 2 different databases, and none of the table names used is repeated on both databases).

Already pressed the verify database button a few times... but the problem persists... is there a way to force it to check this table in particular? or this database in particular?(since there are 2 and it might be only checking the other one)

I have tested in SQL, if the query to get the data should return fields, and it does...

edit
I started to redo the whole report, and apparently that formula field is does not display its value correctly only if I add code to another formula fields (fields that use the same database but other data tables)

@Nome

Ucase({tblPessoas.Nome} & " " & {tblPessoas.NomeIntermedio} & " " & {tblPessoas.Apelido})

@Morada

Ucase({tblMoradas.Morada})

@CPostal

IF ISNULL({tblMoradas.LocPostal}) THEN
{tblMoradas.CPostal} & "-" & {tblMoradas.CPostalAux}
ELSE
{tblMoradas.CPostal} & "-" & {tblMoradas.CPostalAux} & " " & {tblMoradas.LocPostal}

In my mind this makes no sense at all...

edit2
these 3 fields are in the Group Header section while the other one is in the details section... can that be the problem?

Sections

Report

The fields that are in conflict for some reason are @N_Apolice (in the details) and @Nome, @Morada, @CPostal on the header.

All the other fields in the details section come from another data source and work well

enter image description here

Have you checked whether the field is present in table in database? Also what is the error? — Siva, Jun 04 '15 at 09:22
It does exist in the database, it doesnt give me any error it simply does not display any field from this table — RagnaRock, Jun 04 '15 at 09:33
have you checked the linking? may be its making the data be not selected in report. — Siva, Jun 04 '15 at 11:15
I have checked the linking... and its ok... at least in other report it works fine, I can even access the table I want. But I dont know whats different in these 2 reports to have this different behavior — RagnaRock, Jun 04 '15 at 11:22
I can't see the generated sql... so I'm not sure about that, As far as I can tell the parameters are not used to get the data, just to filter it and to format it. (I have 4 parameters, a date used for filtering, the number os decimal cases and the decimal and millions separations chars) — RagnaRock, Jun 04 '15 at 11:34
I started to redo the whole report, and apparently that formula field is does not display its value correctly only if I add code to another formula fields (fields that use the same database but other data tables) In my mind this makes no sense at all... — RagnaRock, Jun 04 '15 at 16:54
no... if I use formulas for any of the other 3 fields... this one stops working... but the other work fine... I dont get whats the relation with them... they use fields from different tables. I added the code for the other fields in the main question — RagnaRock, Jun 04 '15 at 17:10
Try this formula `LEFT(CSTR({tblRamos.CodRamo}) & "000"), 3)` or `RIGHT("000" & TRIM({tblRamos.CodRamo} & " "), 3)` ;). — shA.t, Jun 07 '15 at 03:32
why don't you show a screenshot of the report? and explain what you need. Its difficult to answer assuming things. — Siva, Jun 08 '15 at 08:58
I've added the screenshot, I hope it helps... I think the problem has something to do with me using 2 diferent data sources — RagnaRock, Jun 08 '15 at 16:03
If you add a field from `tblRamos` directly inside your report is shows any value? - If you change your formula field to use some values like `Ucase("test")` for `@Morada` formula fields shows your expected results? — shA.t, Jun 09 '15 at 03:45
I can see values from tblRamos only if I delete "@nome" "@morada" "@cpostal".... otherwise I dont see any value. Even if I put this field in the group header instead of in the details were it is now... it doesnt show anything unless I delete the other fields — RagnaRock, Jun 09 '15 at 08:47

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

I can write as comment but its is becoming huge and wrote as answer

Brother I can see it as a clear linking issue with your tables...

check the linking between the tables tblPessoas, tblMoradas, tblCodPostal and tblRamos... because your formula Morada and CPostal are coming from table tblMoradas and Nome is coming from table tblPessoas and finally problamatic field N_Apolice is coming from tblRamos which all belongs to same datasource....

Now the main problem is when 3 formulas are added then one that is in detail is not displaying... which I think is because of linking becuase when you include the fields from other tables other than tblRamos then the linking from those tables will be included in query which is preventing data from tblRamos not displaing in reports.....

Now when 3 formulas are deleted and field from tblRamos is included then those 3 tables are not included in query that is the reason you can see some data...

You can try your self by checking query with including 3 tables first and removing those tables again. If you are still not sure then fire same queries on datbase and check the rows extracted..

Solution:

The solution I suggest is to check the linking first and if there is any inner join to the tblRamos from other tables then remove that and include outer join to table tblRamos and check the result and according to me that should work. It its working then check indetail database linking and data you want to display. To my knowlege this should work.

Let me know if my approach worked for you.

thanks for work in trying to understand my problem and to help me. But I dont know how to control the joins, wich of the link options should I choose? — RagnaRock, Jun 09 '15 at 15:25
remove the fields in header and check the query... include the fields in header and check the query... and observe any difference in joins — Siva, Jun 09 '15 at 15:35
@RagnaRock can you check tabrecibo and tblramos linking... from the picture it looks like linking of the fields is wrong. — Siva, Jun 10 '15 at 08:08
that is ok. Like you mention, it probably uses some diferent query to fech data from the table tblRamos... I've tried to play a bit with the link options (keeping the same links as in the image) but it didnt work — RagnaRock, Jun 11 '15 at 14:05

Formula field not showing in crystal report 8.5

1 Answers1