external_record_content_get Function

[elopez]

Hi!

I'm working on a database that imports it's records from different files. What I'm trying to achieve is to do the following code:

There is a table (podsaltemp) with a field (Man) that repeats its value about 20 times. It's a character field with the following structure as an example �000012�. I want to add all this characters to an array.

Dim tbl as P
Dim ManTemp as C
Dim Shared ManArr as C

tbl = table.open("podsaltemp")
ManTemp = tbl.record_content_get("Man")

Then I found this method to remove the duplicates.

ManTemp = sortsubstr(ManTemp, crlf() )
ManArr = *remove_duplicates(ManTemp)


After having this list, I would like to use each value (in order) to add another field (Fob with a structure of 000.00 as Number), with which I found this function useful,

Dim TotalMan as N

TotalMan = TABLESUM("podsaltemp.dbf", "Man = '� + ManArr +�' ", "Fob")

Now what I'm missing (if the code is right, because I have no way to check if it's working the way I'm expecting because of the missing code fragments) is to add only the FOB of the records that apply according to the following �code�:

Two fields must be checked, Man and Pro

If Man = ManArr and Pro = .first(P) (I assume this is the way to check that the first letter of the Pro field is a P) Then
Add the FOB to TotalManP
Else, If Man = ManArr and Pro = .first(T) Then (trying to nest the flow control)
Add the FOB to TotalManT
Else, If Man = ManArr and Pro = .first(B) Then
Add the FOB to TotalManB
End If

At the end, I hope to have 3 total values.

This must be done for all the numbers stored in the array at the beginning, so I suppose there must be a For flow control.

So if I have lets say, 30 different numbers in the Man field, and 3 possible totals from each, I would need a place to store this in a order way (may be an array, or a table) to then take them and post them in a report.

It's pretty complex... but if you happen to know just a tiny part of the solution for the whole problem, that would be great.

Thank you in advance, I had been breaking my head with this problem for many hours.

Enrique.

[Stan Mathews]

Re: external_record_content_get Function

Can anybody help me display what this function is storing in the variable?

Use the exact same code in the interactive window, using the return key at the end of each line.

Then use

? list

to see what the list variable holds.

[Ted Giles]

Re: external_record_content_get Function

Enrique.
Have you considered using the Join Operation using an Expression?

[elopez]

Re: external_record_content_get Function

Stan,

Thank you for the advise, it's the first time I use the interactive window, because I didn't understood it.

Ted,

I just began learning A5, I'm still exploring options so I'm applying what I would do in other programming languages. I'll try that method, but because the number that links both tables (from MAN field) changes every time, so it would need somehow a counter or something similar. That's the reason why I want to first read that field from the file, store it in a list, remove it's duplicates and then follow that list to be sure all the MAN values are processed.

[Ted Giles]

Re: external_record_content_get Function

Then perhaps I could point you at the Deduplication Operation?
Learning Alpha takes a bit of time, as you probably know by now, and developers coming from other languages have a hard time getting to grips with the built in facilities. Access users seem to have a lot of trouble initially.
Please let us know how you achieve what you are after, as we are all learning - all the time!

[Al Buchholz]

Re: Code problem with Record manipulation

Ted

You are referring to the DeDupe Utility - Delete Duplicate Records?

dedupeutility.PNG

[Ted Giles]

Re: external_record_content_get Function

No Al, I was in the Operations Tab.

[Mike Wilson]

Re: external_record_content_get Function

Enrique,
I'll try and help for the parts I can follow. Notes are in the code.

' collect the variables for table name, and field names
' >>>> I did this so I could use a table of mine for testing
	dim tablename as C="podsaltemp"    
	dim field1 as C="MAN"
	dim field2 as C="FOB"

'Open the table and obtain a list of the unique values in the MAN field
' >>>>>  You don't have to remove duplicates if you only obtain unique values
	Dim tbl as P
	Dim ManArr as C
	tbl = table.open(tablename) 
	ManArr = tbl.record_content_get(field1,field1,"unique_key_value()")
		'msgbox(manarr)
        tbl.close()
	
'>>>>>  At this point you have a list of values, delineated by crlf() in the variable ManArr

' "I would like to use each value (in order) to add another field (Fob with a structure of 000.00 as Number), 
'   with which I found this function useful, 

	'Dim TotalMan as N
	'TotalMan = TABLESUM("podsaltemp.dbf", "Man = '” + ManArr +”' ", "Fob")

'>>>>   this wont work because the variable ManArr is a list of values not an individual value.
'  Your filter expression in the Tablesum function won't work.
'  You will need to loop through the ManArr list and get the sum of all the FOB for each MAN
'  convert number to character, then concatenate them into a string with a delineator 
'  (...) to show them.

	dim foo as C
	dim vfilter as C
		
	dim vc_Man_FOB_list as C=""
	for each foo in ManArr
		vfilter=field1+"="+chr(34)+foo+chr(34)
		vc_Man_fob_list=vc_Man_FOB_list+alltrim(foo)+"..."+ltrim(str(TABLESUM(tablename,vfilter,field2),10,2))+crlf()
	next

' Show the contents of the output variable
	msgbox(vc_Man_FOB_list)

[elopez]

Re: external_record_content_get Function

Mike, I want to thank you for the code you just posted. It has shown me how to manage the variables within the functions. Also, you have solved a great part of my issue.

Right now I'm working on adapting the code to my data base. I'll post it when I get it right so you can look at the final result.

Again, I thank you a lot for your help. It has been a huge advantage.

Enrique.