I want to import a Excel data to my custom Header Table and Lines table
OK, noted. And do you need something from us?
1 Like
yes, how do I do that?
If we forget your custom tables for a moment, do you know how to import data to F&O at all? There is the Data management workspace for this purpose; Excel is supported as a source file format.
I don’t know about that but I have written this code to import the excel data once the import button is clicked on the form.
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;
class HSOEImportSerialTraceData extends RunBase
{
Dialog dialog;
HSOEHeaderTable headerTable;
HSOELineTable LineTable;
Filename textFile;
FormBuildStringControl frmBldJourName;
private const str OkButtonName = "OkButton";
private const str FileUploadName = "FileUpload";
#Define.xslx(".xlsx")
public static void main(Args _args)
{
HSOEImportSerialTraceData import = new HSOEImportSerialTraceData();
if (import.prompt())
{
import.Headertable(_args);
import.run();
import.callerRefersh(_args);
}
}
public void headerTable(Args _args)
{
Headertable = _args.record();
}
/// <summary>
/// this method is used for creating the dailog box
/// </summary>
/// <returns></returns>
public Object dialog()
{
DialogGroup dialogGroup;
FormBuildControl formBuildControl;
FileUploadBuild dialogFileUpload;
;
dialog = super();
dialogGroup = dialog.addGroup("File path");
formBuildControl = dialog.formBuildDesign().control(dialogGroup.name());
dialogFileUpload = formBuildControl.addControlEx(classstr(FileUpload), FileUploadName);
dialogFileUpload.style(FileUploadStyle::MinimalWithFilename);
dialogFileUpload.baseFileUploadStrategyClassName(classstr(FileUploadTemporaryStorageStrategy));
dialogFileUpload.fileTypesAccepted(#xslx);
dialogFileUpload.fileNameLabel("Select file to upload");
return dialog;
}
/// <summary>
/// Following methods is required to Enables or disables the dialog Ok button.
/// </summary>
/// <param name = "_dialog"></param>
/// <param name = "_isEnabled"></param>
protected void setDialogOkButtonEnabled(DialogRunbase _dialog, boolean _isEnabled)
{
FormControl okButtonControl = this.getFormControl(_dialog, OkButtonName);
if (okButtonControl)
{
okButtonControl.enabled(_isEnabled);
}
}
/// <summary>
/// This method is used for form control
/// </summary>
/// <param name = "_dialog">_dialog</param>
/// <param name = "_controlName">_controlName</param>
/// <returns></returns>
protected FormControl getFormControl(DialogRunbase _dialog, str _controlName)
{
return _dialog.formRun().control(_dialog.formRun().controlId( _controlName));
}
/// <summary>
/// This method is used for checking the upload result of file
/// </summary>
protected void uploadCompleted()
{
FileUpload fileUpload = this.getFormControl(dialog, FileUploadName);
fileUpload.notifyUploadCompleted -= eventhandler(this.UploadCompleted);
textFile = fileUpload.fileName();
this.setDialogOkButtonEnabled(dialog, true);
}
/// <summary>
/// Disables the dialog Ok button until the file upload is complete.
/// </summary>
/// <param name = "_dialog">_dialog</param>
public void dialogPostRun(DialogRunbase _dialog)
{
FileUpload fileUpload = this.getFormControl(_dialog, FileUploadName);
fileUpload.notifyUploadCompleted += eventhandler(this.uploadCompleted);
this.setDialogOkButtonEnabled(_dialog, false);
}
/// <summary>
/// to run the new session for dialog
/// </summary>
/// <returns>true</returns>
protected boolean canRunInNewSession()
{
return true;
}
/// <summary>
/// Bussiness flow of the class and execution code
/// </summary>
public void run()
{
System.IO.Stream stream;
OfficeOpenXml.ExcelWorksheet worksheet;
OfficeOpenXml.ExcelRange range;
ExcelPackage package;
int i = 2,rowCount, s=1;
boolean errorFound;
str fileName;
str filePath,fileNameOnly;
filetype type;
MarkupTrans markupTrans;
NoYes sample;
InventSerialId inventSerialId;
ItemId itemId;
real countLine;
LineNum lineNum;
FileUpload fileUploadControl = this.getFormControl(dialog, FileUploadName);
FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();
if (fileUploadResult != null && fileUploadResult.getUploadStatus())
{
textFile = fileUploadResult.getDownloadUrl();
fileName = fileUploadResult.getFileName();
[filePath, fileNameOnly, type] = fileNameSplit(fileName);
stream = fileuploadResult.openResult();
package = new ExcelPackage(stream);
package.load(Stream);
if(package.get_Workbook().get_Worksheets().Count > s)
throw error("This workbook contains multiple worksheets");
try
{
ttsbegin;
i = 2;
worksheet = package.get_Workbook().get_Worksheets().get_Item(1);
range = worksheet.Cells;
rowCount = worksheet.Dimension.End.Row;
while(i <= rowCount)
{
HSOEHeaderTable headerSerialTable;
//select * from headerTable
// vendInvoiceJourLocal = VendInvoiceJour::findFromPurchId(purchid);
//lineTable.clear();
//purchParmLine = PurchParmLine::findTableRefId_IN(purchParmTable.ParmId, purchParmTable.TableRefId);
while select lineTable
join headerTable
where lineTable.JournalId == headerTable.JournalId
&& lineTable.InventSerialId == headerTable.InventSerialId
&& lineTable.JournalId == headerTable.JournalId
{
InventSerialId = range.get_Item(i,1).Value;
itemId = range.get_Item(i,2).Value;
lineNum = LineTable.LineNum;
//shippingDetailLocal = TPZ_ShippingDetails::find(importFileNo);
if(lineTable.InventSerialId == inventSerialId && lineTable.itemid == itemId)
{
ttsbegin;
lineTable.selectForUpdate(true);
lineTable.JournalId = range.get_Item(i,3).Value;
lineTable.SiteId = range.get_Item(i,4).Value;
lineTable.AccountNum = range.get_Item(i,5).Value;
lineTable.Update();
ttscommit;
}
else
{
error(strFmt("Row %1 not inserted,please check",i));
}
i++;
}
}
ttscommit;
}
catch
{
errorFound = true;
throw error("Failed to upload the data.");
}
for(s=1; s<=package.get_Workbook().get_Worksheets().Count; s++)
{
}
}
}
/// <summary>
/// Refresh the form Data source
/// </summary>
/// <param name = "_args">callerRefersh</param>
public void callerRefersh(Args _args)
{
FormRun formRun;
FormObjectSet ds ;
formRun = _args.caller();
ds = formRun.dataSource();
if(ds)
{
ds.research(true);
}
}
}
You really should learn about the standard features, so you don’t have to waste time with writing code for something that the system can do for you and with much more powerful way.
The standard solution supports multiple data formats (e.g. you can switch to XML by mere configuration instead of developing the whole thing from scratch), batch processing, recurring integration, possibility to fix data in staging tables inside F&O and so on.
Also, when you define data entities for your tables, you can use them for many more features than just import. You can export the data too, edit it directly in Excel connected to F&O, access in Power Automate and much more.
Here is the documentation you can start with: Data management overview.
1 Like
okay I went through that but I want the import to trigger once I click on the import button i lines form. please do check the code
If you’re happy with your solution, so what do you want to discuss in this forum? It’s not clear to me what kind of check you’re asking me for.
1 Like
the code what I have written shows an error , unable to cast object of type linetable’ to type headertable’.
Aha, so you wanted to ask about an error in your code and not how data can be imported to F&O. All right, so let’s look the right problem.
First of all, give us more information. You said you get an error, but not where. Which is the problematic line of code?
In general, the error means that you’re using a value of a wrong type, e.g. assigning an instance of ClassA to a variable that can hold ClassB objects only. When you know where the error is, checking the types should be your next step.
public void headerTable(Args _args)
{
Headertable = _args.record();
} I think in this part but I don’t have any idea if the code is correct or not.
The type of Headertable variable is HSOEHeaderTable. I don’t know what you have in _args.record(), but it doesn’t seem to be an HSOEHeaderTable record. Debug this part to see what you’re getting.
By the way, you can set the Data Source property of the menu item button used for calling the class to pass a record from a particular form data source.