Can't find dollar amounts in sql database

replied on November 17, 2015

The field would just be a number field, the currency is just a format on top of that. So they'll be in the num_val column.

0 0

View 5 previous replies

replied on November 17, 2015 • Show version history

Thanks Miruna,

Nearly all of the values in the num_val column are null. Do you know why this could be? Nearly all of the date_val columns are null as well.

0 0

replied on November 17, 2015

That's fine, those fields are either text fields or empty (the field was just added with the template but does not have a value yet). Each row represents a field value. Only one of the _val columns would have a value in each row.

0 0

replied on November 17, 2015

We've done a test where we submitted a test value for the dollar amount and it does not come up anywhere in the num_val column. I did a search for that value and it does not appear at all in any of the tables in the database either.

When I view the entries in the propval table with the prop_id that I need to do a report on, All of the rows in the num_val, date_val, and bin_val columns are null.

0 0

replied on November 17, 2015

Can you get a screenshot showing that the value was set properly on the field by viewing the metadata for the document in the Laserfiche Client? Next, check the Laserfiche Administration Console and confirm the "type" that your currency field is.

Lastly, if you know the entry ID of the document in question, what if you query the propval table of your repository database with just

select * from propval where tocid=[your entry id]

Do you see the various other field values set on that document and is it only the currency value that you don't see at all in any of the returned rows and columns?

0 0

SELECTED ANSWER

replied on November 17, 2015

I just triple-checked that this is where it's storing my currency value of $12,345.00:

SELECT tocid, prop_id, num_val
  FROM propval
  where tocid = 508569 and num_val is not null

tocid prop_id num_val
508569 907 12345.00000

Maybe there's something wrong with your query? Are you sure you're running it against the right database?

2 0

replied on November 18, 2015

Alexander, is there a way to find the entry id of a document outside of the sql table?

I confirmed that the metadata type is set as "number" and "currency" as well.

I see some date values but they are not there for all of the entries. There are very few amount values in the entire table as well. When I run a query to view the prop_id that I am interested in doing a report on, none of the entries have any values in the num_val, date_val, and bin_val columns.

Amount.JPG (10.21 KB)

| Download

null values.JPG (37.1 KB)

| Download

0 0

replied on November 18, 2015

The entry ID is displayed in the document properties in the Client. There's also a column for it that you can add to your Client. Viewing it requires the "extended properties" feature right.

0 0

replied on November 18, 2015

Thanks everyone for your help. I believe I have the issue figured out now. The prop_id I was querying did not pull up num_val values because those are linked to a different num_val. By querying by tocid I am able to see the num_val values.

0 0

Question

Question

Can't find dollar amounts in sql database

Answer

Replies

Sign in to reply to this post.