Irregularity in field behaviour

[Jondb]

I'm completely baffled by a particular field behaving differently in different records. I have a field in table B called 'currency' which looks up its value from a field in table A. Sample values are USD, GBP, HKD, etc.

The lookup works fine. The problem arises when I create calculations which include the 'currency' field. In some records the calculation produces a valid result, in others it returns zero. I've checked everything I can think of, including:

-re-entered the values in the currency field, both manually and using 'replace field contents' to ensure uniformity

-used various methods to test calculations, including creating calculation fields and also running test scripts

-Calculations that don't include the 'currency' field run consistently across all records

-There's no discernible pattern to the currency field in the records that cause problems; eg some of the 'GBP' records work, others don't; some of the 'USD' records work, others don't, etc.

Can anyone suggest something I may have missed?

[QuinTech]

Jon, can you post the calculation that you are using?

J

[Jondb]

Here's the simplest of the calculations I've tried. This is for a calculation field which simply needs to show a current exchange rate from another table named 'exchange rates', for a range of non-euro currencies, depending on which currency features in the current record. This works in some records and not in others, but the split is not between different currencies; ie it returns a value in some 'GBP' records and not others, and the same with the other currencies. I've checked that spelling etc are consistent in the 'currency' field. The calculation is unstored and result is a number. There's plenty of room in the field for all amounts to display.

Case(Currency = "USD";Exchange rates::USD;Job sheet::Currency = "GBP";Exchange rates::GBP;Currency = "HKD";Exchange rates::HKD;Currency = "CAD";Exchange rates::CAD;1)

[comment]

Why does the 2nd case refer to another, related, field (Job sheet::Currency)?

[QuinTech]

The same calculation you wrote above but reformatted:

Case(



  Currency = "USD";

   Exchange rates::USD;



  Job sheet::Currency = "GBP";

   Exchange rates::GBP;



  Currency = "HKD";

   Exchange rates::HKD;



  Currency = "CAD";

   Exchange rates::CAD;



   1

)

Looks fine to me.

I'd ask the same question as comment, as this seems out of line. But since you have selected 1 as the default value, even if "Job sheet::Currency" is the wrong code, you should still not be returning values of 0, unless one of your exchange rates is, in fact, 0.

[QuinTech]

The same calculation you wrote above but reformatted:

Case(



  Currency = "USD";

   Exchange rates::USD;



  Job sheet::Currency = "GBP";

   Exchange rates::GBP;



  Currency = "HKD";

   Exchange rates::HKD;



  Currency = "CAD";

   Exchange rates::CAD;



   1

)

Looks fine to me.

I'd ask the same question as comment, as this seems out of line. But since you have selected 1 as the default value, even if "Job sheet::Currency" is the wrong code, you should still not be returning values of 0, unless one of your exchange rates is, in fact, 0.

[QuinTech]

The same calculation you wrote above but reformatted:

Case(



  Currency = "USD";

   Exchange rates::USD;



  Job sheet::Currency = "GBP";

   Exchange rates::GBP;



  Currency = "HKD";

   Exchange rates::HKD;



  Currency = "CAD";

   Exchange rates::CAD;



   1

)

Looks fine to me.

I'd ask the same question as comment, as this seems out of line. But since you have selected 1 as the default value, even if "Job sheet::Currency" is the wrong code, you should still not be returning values of 0, unless one of your exchange rates is, in fact, 0.

[QuinTech]

Another thought: Looking at your calc, i am guessing that you have a table, to which your main table is related, called Exchange Rates. It looks like you have several different fields: USD, GBP, HKD, ...

Your situation may be different, but it seems to me that you would want such a table to have two fields: a key field {USD, GBP, HKD, ...} and a Rate field {1.18, .89, 1.02, ...} This would be a more typical structure, rather than having each record show exchange rates for multiple currencies. An exception to that would be if the records represent something besides National Currencies, but given the name of your table i don't believe that is the case.

So i'd have one record with the key name USD and a rate of 1 (because the exchange rate for me to American dollars is 1, since i'm in the US), another record with the key name CAD and a rate of .813 (because the US to Candian exchange rate is .813 today, i think), etc.

J

[QuinTech]

Another thought: Looking at your calc, i am guessing that you have a table, to which your main table is related, called Exchange Rates. It looks like you have several different fields: USD, GBP, HKD, ...

Your situation may be different, but it seems to me that you would want such a table to have two fields: a key field {USD, GBP, HKD, ...} and a Rate field {1.18, .89, 1.02, ...} This would be a more typical structure, rather than having each record show exchange rates for multiple currencies. An exception to that would be if the records represent something besides National Currencies, but given the name of your table i don't believe that is the case.

So i'd have one record with the key name USD and a rate of 1 (because the exchange rate for me to American dollars is 1, since i'm in the US), another record with the key name CAD and a rate of .813 (because the US to Candian exchange rate is .813 today, i think), etc.

J

[QuinTech]

Another thought: Looking at your calc, i am guessing that you have a table, to which your main table is related, called Exchange Rates. It looks like you have several different fields: USD, GBP, HKD, ...

Your situation may be different, but it seems to me that you would want such a table to have two fields: a key field {USD, GBP, HKD, ...} and a Rate field {1.18, .89, 1.02, ...} This would be a more typical structure, rather than having each record show exchange rates for multiple currencies. An exception to that would be if the records represent something besides National Currencies, but given the name of your table i don't believe that is the case.

So i'd have one record with the key name USD and a rate of 1 (because the exchange rate for me to American dollars is 1, since i'm in the US), another record with the key name CAD and a rate of .813 (because the US to Candian exchange rate is .813 today, i think), etc.

J

[Jondb]

Thanks for your comments - haven't had a chance to look at this in the past week.

Firstly, the reference to the 'job sheet' table is one I left in by mistake when I was playing around with different calculations. The table that is calling the calculation is called 'job sheet', so this reference is superfluous.

My exchange rate table is set up using only one record, which I update manually each day, and which contains fields for each of the currencies I need to convert. Maybe this is a clumsy way of doing things.

Your idea of using different records makes sense. I could create a list view layout to browse and alter them. I still can't figure out why my results are so inconsistent, but maybe that will fix it.

Thanks,

Jon

[Jondb]

Thanks for your comments - haven't had a chance to look at this in the past week.

Firstly, the reference to the 'job sheet' table is one I left in by mistake when I was playing around with different calculations. The table that is calling the calculation is called 'job sheet', so this reference is superfluous.

My exchange rate table is set up using only one record, which I update manually each day, and which contains fields for each of the currencies I need to convert. Maybe this is a clumsy way of doing things.

Your idea of using different records makes sense. I could create a list view layout to browse and alter them. I still can't figure out why my results are so inconsistent, but maybe that will fix it.

Thanks,

Jon

[Jondb]

Thanks for your comments - haven't had a chance to look at this in the past week.

Firstly, the reference to the 'job sheet' table is one I left in by mistake when I was playing around with different calculations. The table that is calling the calculation is called 'job sheet', so this reference is superfluous.

My exchange rate table is set up using only one record, which I update manually each day, and which contains fields for each of the currencies I need to convert. Maybe this is a clumsy way of doing things.

Your idea of using different records makes sense. I could create a list view layout to browse and alter them. I still can't figure out why my results are so inconsistent, but maybe that will fix it.

Thanks,

Jon

[comment]

How is your relationship to the Exchange rates table defined now?

[comment]

How is your relationship to the Exchange rates table defined now?

[comment]

How is your relationship to the Exchange rates table defined now?