Two functions I can’t work out.

[Lyndon Willcox]

Hi

Very new to TP

I’ve designed a very simple stock inventory solution.
One session to scan, fields includes Current stock.
Description
Scan qty.

The other session holding lookup table.

1. I need to prevent the software from adding a product/barcode that is not present in the product/lookup table.

2. When you scan the same product in different areas, eg 2 and 3, I need a field to sum the previous entries and display the total counted eg 5. This figure is for reference only.

Can anyone help
Many thanks

Lyndon

 

[Dan Peluso]

Hi,

1. You should be able to use validation for this. So, for example, if you have a DESCR being returned from your lookup session, you could validate this field to be a Min Length=1. With that validation, it would fail to submit/save if the Item was not found (b/c Descr would be blank on a failed lookup). Of course, there are other ways to do this and this also assumes that no item descriptions are blank in the lookup data and it also assumes that your lookup "Not Found" value is set to blank. In any event, this is just one of a few different suggestions revolving around using the validations to ensure your data is correct.

2. This might be a good case for using a Custom SQL variable field. If you add a field and make it a VARIABLE field type, one of the option is to use Custom SQL. An example of custom SQL that might work for you is as follows:

SELECT SUM(CAST(Field3 as INT)) AS s FROM Session1 WHERE Field1='[*0*]',

This is assuming that your ITEM NUMBER field is field 1 and that your qty field is field 3. For reference, I made this Variable-->Custom SQL field as field 4 but that does not technically matter. If your field positions for qty are different, it would affect the string "Field3" in the SUM area. If your item field is not field 1, that will affect the "WHERE Field1=" and the "[*0*]" would need to change to whatever the zero based field index is for your item field.

Let us know if that helps or if you have any other question.

 

[Lyndon Willcox]

Hi Dan

Your first fixed worked a treat.
Still struggling on the second a bit
Think Ive done it ok, but no joy as yet.
I have attached two screen shots,wondered if you would check for me.

Regards
Lyndon

 

[TracerPlus Support]

Hi,

Three things jump out at me.

1. You are attempting to sum Field11 but you only have 5 fields in this session. I am guessing the qty field you wish to sum is the third field so you should replace "Field11" with "Field3".

2. You are referencing the table name as the session name you provided but the table name in SQL is always going to be "Session1" regardless of the name you assigned.

3. It looks like you are attempting to filter the WHERE clause on field5; your location field. From your original description, I thought you wanted to sum all of the same PRODUCT regardless of location.

Something like below seems more specific to your config.

SELECT SUM(CAST(Field3 as INT)) AS s FROM Session1 WHERE Field1='[*0*]',

 

[Lyndon Willcox]

Hi Dan

Its killing me, just cant get it to work.

I have named the field as 4 as this is the field I need to sum. Think this is correct but still nothing shows in the field.
Can you see anything else that may be the issue.

SELECT SUM(CAST(Field4 as INT)) AS s FROM Session1 WHERE Field 1='[*0*]',

Also attached current screen shots and project

Regards
Lyndon

 

[OliverR]

Hi Lyndon,

If you want to share your project you should do this by exporting it (in TracerPlus desktop go the File menu, then choose Export). This will generate a .tpe file that you can then upload to this forum; the .tpe file will include your entire project. Uploading just the .tpp file like you did does not work, we can't open that.

I'm not a TracerPlus dev and so not as knowledgeable, but I'll be happy to take a look at your project to see if I can find out what the problem is.

EDIT: also, one thing I notice, is you've got a comma at the end of your query that shouldn't be there. Try removing that and see if that makes a difference. The problem with custom SQL queries in TracerPlus, I've found, is you don't get any errors if your syntax isn't correct; it just doesn't work. That makes it hard to troubleshoot

 

[Lyndon Willcox]

Thanks for the info
I will post, here a copy as suggested.

Regards
Lyndon

 

[Lyndon Willcox]

If anyone can help

I have a field on my scanner, showing the sum of the units I have scanned. This is a running total for information only. But I cannot get it to work.
Can anyone help.

 

[TracerPlus Support]

Hi,

Ok. I found the issue(s).

1. Your "Field 1" in the WHERE clause has a space but it should be "Field1" (no space).
2. There is a trailing period that Oliver pointed out and should be removed (copy/paste error I imagine).

Thanks, Oliver for pointing it out. I didn't notice it myself.

**We do have an existing request to return better error messages on bad SQL but not sure of the status and/or resolution of that at the moment.

 

[Lyndon Willcox]

Hi again

What eagle eyes!
Great stuff, it works a treat.
Thanks very much for your time and help really appreciated.

Where can I find details of coding and code building. I am familiar with Access, but this is different again.
eg SUM(CAST(Field4 as INT))

Many Thanks
Lyndon

 

[Dan Peluso]

Hi,

Great news. The eagle eye mostly belongs to Oliver .

The custom SQL options in TracerPlus support the common SQL language that is used by either SQL CE on Windows Mobile or SQL Lite on Android.
Both of these databases do support most SQL commands but there are a few exceptions. Any SQL documentation found on the web should be usable with just those few exceptions.