Joining tables? Or a set? And using a table for pictures.

[paintacar]

I have a question about joining tables or creating a set. I have a main table with repair order (ro) as the primary key and the other table is for picture files. The main table I filled out to use as a sample database to build my app, in it there are several fields that I am not even sure will be used yet, I made them in case I had a need for them. I thought of breaking up the main table to make it easier to navigate thru and to put type of information in different tables. Such as using a table only for the pictures for the jobs. I am thinking that it would be easier to find record information and it may help with any performance issues (not that I know of any, but just thinking I should plan ahead in case there will be about 1000-2000 records added each year). All of the information in each table is related to one specific field the (ro) field. In the second table or if there more tables I want to link the records to the (ro) not every job will need pictures, so how do I do this in simple terms can I use the genie to join them? How do I make a new (ro) in the field in the picture table, does the field have to have the same name? I saw that it wants them to be the same type and size. Do I some how have it duplicate the (ro) value to the linked or joined tables? It does not matter to me if the field is the same name, just that I can see the value when they are linked together and know which record they belong to in the main table. I also want to be able to make a grid with information from both tables. the main table has about 40-50 fields the fields do not have a lot of information in them just mainly one or two words each, the picture table could have 2-20 picture files of jpg in them is what I am planning (another story I don't know if I should just make 20 fields to have in the picture table for that blank so that there is room for 20 files, or do I just have one that all the files go into? Never done this before) Would this be just fine to use one table or is it a good idea to break it up the way I am planning? The end result should only be returning back and average of 30-100 records at a time on the grid view because there should only be about that many jobs being tracked at any given time ( filtered by a value that will tell to show the records or not in the current grid based on whether or not the value they are completed --I assume thats how I would show only the current jobs, but that would be another post).

[Davidk]

Re: Joining tables? Or a set? And using a table for pictures.

Are you storing the images themselves in the table or a reference to a full path where the images are stored. If the images are being stored in the table then you must decide how large you want your tables to be - try to figure out how large the images are. If they're small then the main table would be fine. If the images are large then you may have trouble displaying them in a grid AND the tables would get too big.

Further, is there more than one image per repair order? If so, then rules dictate that they should be stored in a related table... 1 repair order to many images for that repair order. Then your View might be used to access all data. Again, the images table could store the image itself or just a reference to the image.

You say 2-20 picture files of jpg - is that per record? If so, then you should use a related table. The fields might be RO, ROImageId, Imagename with the key being a combination of RO and ROImageId. If it's 20 Images for 1 repair order then it's 20 image records for 1 repair order... not 20 fields in one record - that would be unmanageable.

Hope that helps a bit.

[paintacar]

Re: Joining tables? Or a set? And using a table for pictures.

yes the images will be stored in the table not just a reference. The images may vary in size,but I thought that in the image uploader that you could specify the output size? The one thing I noticed about the image uploader is it seemed to me that you can only upload one image at a time, so I was thinking that I may have to use the file uploader if they take 3-4 pictures with their phone then they can select them then upload them, but then the file size is not restricted is it? I want the users to be able to upload the images from either a digital camera or from their cell phones from a file on the computer whatever can make the whole idea of using pictures to help the employees the easiest way possible. I expect my app to be used from office computers and possibly from the employees phones. The picture part is just a tool for the employees to document and take a picture note of what they are working on to help them with their reassembly of the jobs. The reason it has to go into a table and be related to the record in the main table is that it will be easy to find any pictures that anyone has taken because it is not always the same person putting the jobs back together. So what you are saying is yes I do have to use a seperate table for the pictures and they would not be per record with each picture in a field or all the pictures in one field, but that they would each be their own record related to the field (ro) of the main table? Whatever it has to be is fine, but how do I get them to automaticly be assigned a number that will be related and linked to the main table? And what you are saying is that the table would only probably have two fields then?

[paintacar]

Re: Joining tables? Or a set? And using a table for pictures.

I really need to know how to join the tables If the main table uses (ro) for the primary key and that really is the only field that can identify the record. How do I set up the second table for pictures if not every record uses pictures how do I link them? if I never had a picture table to begin with how should I set it up to be linked and record records from the grid with a key that will link them without the user adding extra information? Do I have to copy the main table and drop all the fields that I am not using? Then add the new fields? What will link them together and how will it do that automaticly? Forgive me yep I am still New.

[paintacar]

Re: Joining tables? Or a set? And using a table for pictures.

Just waiting to see if anybody can help here.

[Davidk]

Re: Joining tables? Or a set? And using a table for pictures.

Hey Jerrod,

This is one way I would get this done. Use the "File Upload - User Defined " instead of the Image Upload. I can't see that the Image Upload allows multiple files. The File Upload-User Defined does and you can check to see if the files are binary or not. The code below does not check for binary but that's easily added - all the information you need is in the comments for the function.

You'll need a table to hold your images. Two keys - 1 is the main RO, and the 2nd is just an autoincrement number. A 3rd field will be your image field - a BLOB field - and specifically a LONGBLOG field. I had no luck with just a BLOB field - maybe it was just me but when I changed the type to LONGBLOB I started getting data into the field.

Your "File Upload - User Defined" ajax callback will call the function below, which pops up the File Upload dialog and then processes each image you select, writing a record out to your new table.

Let me know if you need more detailed information. Lots more error checking is required, but this gets the upload and save done.

function processSQLfiles as v (e as p)

'This function is called after the file(s) selected in the Upload Files dialog have been uploaded to the server.
'The function is responsible for processing each uploaded file.
'The passed in 'e' object contains these properties

'e.fileCount - the number of files that were uploaded
'e.fileArray - a property array with one entry for each uploaded file
'e.uploadProperties - Contains multiple sub-properties for all of the properties set in the Action builder.
'e.rv  - Request variables
'e.tmpl - pointer to the Grid definition
'e.rtc - a pointer variable that contains run-time calculations
'e.__si - state information
'e.session  - session variables
'e.rowNumber  - row number of the Grid row that has focus. If val(e.rowNumber) < 0, then it is a new record row.
'e.part - either 'G' (for 'Grid')  or 'D' (for 'DetailView'). Test left(e.part,1) = "D" .or left(e.part,1) = "G"
'e.primaryKeyArray - an array of primary key values for the current row's primary key. If the primary key is based on a single field (or record number in the case of a .dbf table), the array will have one entry. If the primary key comprises multiple columns, the array will have an entry for each column in the primary key.

'e._isLinkedGrid - .t. if the Grid is a linked Grid (i.e. it has a parent Grid)
'e._linkValues = linking value. Contains a CRLF delimited list of linking values in this format: fieldname|type|linkingValue



'Each entry in the e.fileArray array has these propeties (where 'i' is between 1 and e.fileCount):
'e.fileArray[i].file.characterSet - character set of the uploaded file
'e.fileArray[i].file.contentType - MIME type (e.g. 'application/octet-stream')
'e.fileArray[i].data - data that was uploaded. This will either be binary data or text data. Check the .file.dataType property
'e.fileArray[i].fileName - filename of the file on the client machine
'e.fileArray[i].encoding - For text files, specifies the encoding type
'e.fileArray[i].file.dataType = Either 'text' or 'binary'

'If you want to send any Javascript back to the browser to execute after this event has completed, you can set this property:
'e.javascript 
'EXTREMELY IMPORTANT - The Javascript that is sent back to the browser is executed in the context of the IFrame that contains the File Select window.
'Therefore, in order for the Javascript you send back to invoke methods of the Grid object, you must prefix all methods with 'window.parent'.
'For example: window.parent.{grid.object}.refresh()

'You can set e.abort = .t. to abort the operation. If you set e.abort to .t. then you can set e.errorText to the text you want to display to the user.


dim newProdId as C
dim cn as SQL::Connection
dim myargs as sql::arguments
dim sql as C
dim flag as l
dim cs as C
dim count as N
dim i as N

count = e.filecount

newProdId = e.primaryKeyArray[2]
	
cs = e.tmpl.cs.connectionstring
if .not. cn.open(cs) then
    e.javascript = "alert('Error opening SQL Connection');"
    end 
end if

for i = 1 to count
	myargs.set("PId", newProdId)
	myargs.set("PFId", i)
	myargs.set("PImage",e.fileArray[i].data)
	
	sql = "INSERT INTO tblProductFeatures SET ProductId = :PId, ProdFeatId = :PFId, ProdImage = :PImage"
	flag = cn.Execute(sql,myargs)
		
	if flag = .f. then
		e.javascript = "alert('Error executing SQL Statement');"
		end 
	end if
next i
	
e.javascript = "alert('All files processed');"
	
end function