Unique Field values

[Chris_Wood]

I'd like to place list of unique field values into an array or a CR-LF string. All I can manage to return are unique records - by using initialize_from_table inconjuction with "unique_key_value()".

for example the table contains:-
Po_no, style_no
1234, ABC
1234, DEF
1234, DEF
5678, ABC
5678, ABC

unique_key_value() gives:
1234, ABC
1234, DEF
5678, ABC

what I would like is just a unique list of Po_no's
1234
5678


Any ideas?

many thanks

Chris

[Tom Cone Jr]

Re: Unique Field values

Chris, it seems to me that you've got two issues here.

1) how to extract only records that have a unique po_no field value?

2) how to store only those unique po_no field values into an array?


It seems to me that one approach to this would be to define a new index on your table. Filter on the PO_NO field, but include UNIQUE values only. (i.e. not ALL).

Your script could open the table, set the new index primary, use a pointer to this instance of the table in the <arrray>.initialize_from_table() method, and then close the table. The property array would contain a single entry (row?) for each uniqiue PO_NO field value. You'd extract the field value from each entry in the property array like this:

curr_po_no = my_array[element_number].po_no

-- tom

[Chris_Wood]

Re: Unique Field values

Thanks Tom,

Yes, that sums it up nicely.

Its going to take me a while to get my head around what you just suggested but I'll give it a go.

Chris

[Chris_Wood]

Re: Unique Field values

Tom,

Got it working a treat, thanks so much. You've also just introduced me to indexes.


Chris

[Tim Kiebert]

Re: Unique Field values

Chris,

Here is another way (not suggesting it is better in any way, just different.)

list = table.external_record_content_get("YourTable","Po_no","Po_no","unique_key_value()")

here is an example from alphasports

list = table.external_record_content_get("product","Vendor","Vendor","unique_key_value()")
?List
= V001 
V002 
V003 
V004 
V005 
V006

The key to making it work(pardon the pun) is that you need to specify the field that you want the unique function to apply to as the order expression.

[Tom Cone Jr]

Re: Unique Field values

Tim said:
The key to making it work(pardon the pun) is that you need to specify the field that you want the unique function to apply to as the order expression.

Thanks, Tim. Your approach reminds me of the need to "order" records in a report layout to match the layout's group break specifications.

-- tom

[Tim Kiebert]

Re: Unique Field values

Tom, my pleasure. :)


Chris, A heads up for future reference. If you want to limit the fields that get initialized into an array from a table use table.openmap() to get the pointer. This function takes a list of fields as an argument.

[Chris_Wood]

Re: Unique Field values

Hi Tim,

Thats great, much shorter code and no need for the index.

The tip for table.openmap() will come in handy, in a former life I used SQL for retrieving sets of data, you've now assuaged my fears of Alpha's capabilities.

Chris