Using data from multiple Datasets

severinmueller

<p>Hello</p><p> </p><p>I am trying to create a report displaying the requests that are on our database. Since I cannot use sql for internal reasons, I created a scripted dataset.</p><p> </p><p>I get the request Id through a parameter passed directly through our application.</p><p> </p><p>I now use 2 datasets, one for the requests and one for every request's part list. Example</p><p> </p><p>request 1 has 5 parts.</p><p>request 2 has 7 parts</p><p>request 3 has 4 parts</p><p>request 4 has 9 parts.</p><p> </p><p>I fetch all the data for the requests with this script</p><pre class="_prettyXprint _lang-js _linenums:1">function getCode(field) { code = codeContainer.findById(field); return code.getShortName();}if(i<requestList.length) { /* objection creation */ requestId = requestList[i]; currentRequest = requestId; requestView = requestViewContainer.findById(requestId); request = requestContainer.findById(requestId); requestDetailFilter = new SortableFilter(); requestDetailFilter.addFilter(RequestDetailMapping.REQUEST_ID,request.getRequestId()); requestDetailList = requestDetailContainer.retrieveByFilterAndOrder(requestDetailFilter); requestDetail = requestDetailList.get(0); installation = installationContainer.findById(request.getInstallationId()); // if the owner company is used in the installation, we need to use it here if(installation.isUserOwnerAddress==true) { company = companyContainer.findById(request.getCompanyId()); } else { company = companyContainer.findById(installation.getOwnerCompanyId()); } buyerCompanyAddress = addressArchiveContainer.findById(request.getCompanyAddressId()); buyerUserAddress = addressArchiveContainer.findById(request.getUserAddressId()); invitedSupplierFilter = new SortableFilter(); invitedSupplierFilter.addFilter(InvitedSupplierMapping.REQUEST_ID, requestId); invitedSupplierList = invitedSupplierContainer.retrieveByFilterAndOrder(invitedSupplierFilter); invitedSupplier = invitedSupplierList.get(0); productBuilder = productBuilderContainer.findById(requestView.getProductBuilderId()); product = productContainer.findById(request.getProductId()); supplierCompany = companyContainer.findById(invitedSupplier.getCompanyId()); supplierAddress = addressContainer.findById(supplierCompany.getAddressId()); supplierUser = userContainer.findById(invitedSupplier.getUserId()); supplierUserAddress = addressContainer.findById(supplierUser.getAddressId()); supplierCountry = countryContainer.findById(supplierAddress.getCountryId()); supplierBuyerInfoFilter = new SortableFilter(); supplierBuyerInfoFilter.addFilter(SupplierBuyerInfoMapping.BUYER_COMPANY_ID,request.getCompanyId()); supplierBuyerInfoFilter.addFilter(SupplierBuyerInfoMapping.SUPPLIER_COMPANY_ID,invitedSupplier.getCompanyId()); supplierBuyerInfo = supplierBuyerInfoContainer.retrieveByFilterAndOrder(supplierBuyerInfoFilter); /* string assertion to avoid null string */ if(buyerCompanyAddress.getStreet1()==null) { buyerStreet1 = ""; } else { buyerStreet1 = buyerCompanyAddress.getStreet1() } if(buyerCompanyAddress.getStreet2()==null) { buyerStreet2 = ""; } else { buyerStreet2 = buyerCompanyAddress.getStreet2() } productBrandContainer = ResourceManager.get(ProductBrandContainer); productBrand = productBrandContainer.findById(requestDetail.getProductBrandId()); filter = new SortableFilter(); filter.addFilter(RequestItemMapping.REQUEST_ID,request.getRequestId()); requestItems = requestItemContainer.retrieveByFilterAndOrder(filter); /************************** GENERAL INFORMATION************************************************************************************/ row["requestDate"] = request.getRequestDate(); // We want the case id in case the request hasn't been sent yet. if((request.getReferenceNumber()==null) || (request.getReferenceNumber()=="") || (request.getReferenceNumber()=="n.a.")) { row["referenceNumber"] = procurementCase.getReferenceNumber(); row["referenceLabel"] = "Reference No*"; row["referenceText"] = "* until the RFQ sent to the supplier, the Case ID will be displayed instead of the Reference Number"; } else { row["referenceNumber"] = request.getReferenceNumber(); row["referenceLabel"] = "Reference No"; row["referenceText"] = ""; } row["invitedSupplierId"] = invitedSupplierId = invitedSupplier.getInvitedSupplierId(); row["installation"] = request.getInstallationName(); /************************** SECTION BUYER ***************************************************************************************/ row["buyerCompany"] = company.getName(); row["buyerCoCompanyName"] = buyerCompanyAddress.getCoCompanyName(); row["buyerUserName"] = request.getFirstName() + " " + request.getLastName(); row["buyerStreet1"] = buyerStreet1; row["buyerStreet2"] = buyerStreet2; row["buyerZIPCity"] = buyerCompanyAddress.getZipCode() + " " + buyerCompanyAddress.getCity(); row["buyerCountryName"] = buyerCompanyAddress.getCountryName(); row["buyerPhone"] = buyerUserAddress.getPhone(); row["buyerFax"] = buyerUserAddress.getFax(); row["buyerEmail"] = buyerUserAddress.getEmail(); if(supplierBuyerInfo.size()<=0) { LOG.debug("No supplier information for this user found skipping..."); } else { row["buyerCustomerNo"] = supplierBuyerInfo.get(0).getSuppliersRefId(); } /************************** SECTION SUPPLIER **************************************************************************************/ row["supplierCompany"] = invitedSupplier.getCompanyName(); row["supplierCoCompanyName"] = supplierAddress.getCoCompanyName(); row["supplierUserName"] = invitedSupplier.getFirstName() + " " + invitedSupplier.getLastName(); row["supplierStreet1"] = supplierAddress.getStreet1(); row["supplierStreet2"] = supplierAddress.getStreet2(); row["supplierZIPCity"] = supplierAddress.getZipCode() + " " + supplierAddress.getCity(); row["supplierCountryName"] = supplierCountry.getName(); row["supplierPhone"] = supplierAddress.getPhone(); row["supplierFax"] = supplierAddress.getFax(); row["supplierEmail"] = supplierAddress.getEmail(); if(supplierBuyerInfo.size()<=0) { row["supplierNo"] = ""; } else { row["supplierNo"] = supplierBuyerInfo.get(0).getAccountingId(); } /************************** SECTION PRODUCT *****************************************************************************************/ row["productCategory"] = requestView.getProductCategoryName(); row["productLabel"] = product.getLabel(); row["productBrand"] = request.getProductBrandName(); row["productType"] = request.getProductTypeName(); row["productBuilder"] = productBuilder.getName(); row["productBuildingYear"] = product.getBuildingYear(); row["productSerialNo"] = product.getSerialNumber(); /************************** SECTION DELIVERY ADDRESS***********************************************************************************/ if(request.getDeliveryAddressId()!=null) { delivery = addressArchiveContainer.findById(request.getDeliveryAddressId()); row["deliveryDesignation"] = (getCode(delivery.getAddressDesignationId())==null ? "" : delivery.getAddressDesignationId()); row["deliveryCompanyName"] = delivery.getCompanyName(); row["deliveryCoComanyName"] = delivery.getCoCompanyName(); firstName = (delivery.getFirstName()==null) ? " " : delivery.getFirstName(); lastName = (delivery.getLastName()==null) ? " " : delivery.getLastName(); row["deliveryUserName"] = firstName + " " + lastName; row["deliveryStreet1"] = delivery.getStreet1(); row["deliveryStreet2"] = delivery.getStreet2(); delZip = (delivery.getZipCode()==null) ? " " : delivery.getZipCode(); delCity = (delivery.getCity()==null) ? " " : delivery.getCity(); row["deliveryZIPCode"] = delZip + " " + delCity; row["deliveryState"] = delivery.getState(); row["deliveryCountryName"] = delivery.getCountryName(); row["deliveryPhone"] = delivery.getPhone(); row["deliveryFax"] = delivery.getFax(); row["deliveryEmail"] = delivery.getEmail(); row["deliveryRemark"] = request.getDeliveryAddressRemark(); } /************************** SECTION INVOICE ADDRESS***********************************************************************************/ if(request.getInvoiceAddressId()!=null) { invoice = addressArchiveContainer.findById(request.getInvoiceAddressId()); row["invoiceCompanyName"] = invoice.getCompanyName(); firstName = (invoice.getFirstName()==null) ? " " : invoice.getFirstName(); lastName = (invoice.getLastName()==null) ? " " : invoice.getLastName(); row["invoiceUserName"] = firstName + " " + lastName; row["invoiceCoCompanyName"] = invoice.getCoCompanyName(); row["invoiceStreet1"] = invoice.getStreet1(); row["invoiceStreet2"] = invoice.getStreet2(); zip = (invoice.getZipCode()==null) ? " " :invoice.getZipCode(); city = (invoice.getCity()==null) ? " " :invoice.getCity(); row["invoiceZIPCode"] = zip + " " + city; row["invoiceCountryName"] = invoice.getCountryName(); } /************************** SECTION VESSEL *******************************************************************************************/ installation = installationContainer.findById(requestDetail.getInstallationId()); row["installationName"] = requestDetail.getInstallationName(); row["installationLloydsNumber"] = requestDetail.getInstallationLloydsNumber(); row["installationBuilder"] = requestDetail.getInstallationBuilder(); row["installationBuildingNo"] = requestDetail.getInstallationBuildingNumber(); row["installationBuildingYear"] = requestDetail.getInstallationBuildingYear(); row["installationPortOfRegistry"] = installation.getPortOfRegistry(); row["installationClass"] = requestDetail.getClassificationDesc(); row["installationType"] = requestDetail.getInstallationTypeDesc(); row["installationVatNumber"] = installation.getVatNumber(); /************************** SECTION REQUEST TERMS **************************************************************************************/ row["deliveryTerms"] = request.getDeliveryTermShortName(); row["deliveryCountryName"] = request.getDeliveryCountryName(); row["deliveryCity"] = request.getDeliveryCity(); row["deliveryDate"] = request.getDeliveryDate(); row["needOfferUntil"] = request.getRequestDurationEndDate(); row["offerValidity"] = request.getOfferValidityDuration(); row["currency"] = request.getCurrencyShortName(); row["paymentTerms"] = request.getPaymentTerms(); row["remarks"] = request.getRequestRemark(); i++; return true;}return false;</pre><p>I get all the information, it works nicely.</p><p> </p><p>What doesn't work is the the second dataset. I cannot get the parts list for every request, i only get the first for each one. I use this script:</p><pre class="_prettyXprint _lang-js _linenums:1">function getCode(field) { code = codeContainer.findById(field); return code.getShortName();} requestId = requestList[currentRequest]; request = requestContainer.findById(requestId); requestView = requestViewContainer.findById(requestId); LOG.info("Current Row: " + request.getRequestId()); requestDetailFilter = new SortableFilter(); requestDetailFilter.addFilter(RequestDetailMapping.REQUEST_ID,request.getRequestId()); requestDetailList = requestDetailContainer.retrieveByFilterAndOrder(requestDetailFilter); requestDetail = requestDetailList.get(0); filter = new SortableFilter(); filter.addFilter(RequestItemViewMapping.REQUEST_ID,request.getRequestId()); requestItems = requestItemViewContainer.retrieveByFilterAndOrder(filter); productBrandContainer = ResourceManager.get(ProductBrandContainer); productBrand = productBrandContainer.findById(requestDetail.getProductBrandId()); row["specHeader1"] = productBrand.getSpecificationHeader1(); row["specHeader2"] = productBrand.getSpecificationHeader2(); row["specHeader3"] = productBrand.getSpecificationHeader3(); row["specHeader4"] = productBrand.getSpecificationHeader4(); row["specHeader5"] = productBrand.getSpecificationHeader5(); row["specHeader6"] = productBrand.getSpecificationHeader6(); row["specHeader7"] = productBrand.getSpecificationHeader7();if(j<requestItems.size()) { item = requestItems.get(j); row["pos"] = item.getPositionNumber(); row["componentDescription"] = item.getDescription(); row["componentExecution"] = item.getComponentExecution(); row["quantity"] = item.getQuantitySI(); row["remark"] = item.getRemark(); row["quantityUnit"] = getCode(item.getUnitSI()); row["spec1"] = item.getBuilderSpecification1(); row["spec2"] = item.getBuilderSpecification2(); row["spec3"] = item.getBuilderSpecification3(); row["spec4"] = item.getBuilderSpecification4(); row["spec5"] = item.getBuilderSpecification5(); row["spec6"] = item.getBuilderSpecification6(); row["spec7"] = item.getBuilderSpecification7(); j++; currentRequest++; return true;} else { return false;}</pre><p>How can I manage to get the parts lists as well and use in my layout? For better understanding, I have attached my rptdesign file. Any help would be appreciated.</p><p> </p><p>Thanks</p><p> </p><p>Severin</p><p> </p>

severinmueller

<p>Never mind, found a solution by checking for the existence of parts in the second data set and only then go fetch them and grouped them properly.</p>