Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

(New) InfoAssist User: How can I create a Virtual Field using Detail Define/Summary Field with multiple joins; that will not compute the entire dataset of the fields selected?


Joshua Mack

Recommended Posts

Hello, everyone! I am new to report building in WebFocus and currently in lieu of obtaining my 1st certification in SQL. I am currently building a consolidation report for the company I work for using WebFocus | InfoAssist not certain of the verison. I don't have administrative access to manipulate the data in syntax, so I'm attempting to do this by InfoAssist Only and I've reached an impasse in my skill set.

Hopefully, the attachment and explanation will suffice, in my effort to get support.

20230120_191336.thumb.jpg.10777ae21e0b6a7659c07068c4ffd94e.jpgThis consolidation report is designed to easily identify opportunities to consolidate locations with EXACT items into one location; decreasing the storage capacity levels, improving inventory accuracy and host of other benefits.

This report design, required three tables to be joined and the fields needed are listed below

  • Inventory
    • Item #, UPC, Description, Lot #, Location & On-Hand Quantity
  • Item Dimensions
    • Unit of Measures and Gross Cubes
  • Location Dimensions
    • Location Character Group, Location Dimensions Groups & Usable Cubic Dimensions

    

The issue I am having with the Summary Field (Compute) UI and the Detail Define (Compute) is, the fields that I need for the virtual field both have multiple rows of records formatted in PACKED. How would I go buy filtering this data to return the correct calculations? (Note: After creating this in Detail Define, the system says that the file is unrecognized but the Summary field will return a number but not the correct one.) If you look at the attached photo I have a field called CAPBYITEM = (Usable Cubic Dimensions / Gross Cubes), which is designed to return the max location capacity by item but in order to return the correct calculations the formula should be as the following.

CAPBYITEM = (Usable Cubic Dimensions by Location Dimensions Group) / (Gross Cubes by Unit of Measure = EACHES)

  • Locations relational tables = (Locations + Location Character Group + Location Dimensions Group + Usable Cubic Dimensions)
  • Items relational tables = (Items + Unit of Measure [Eaches, Inner Packs & Cases] + Gross Cubes)
    • Eaches, Inner Packs and Cases all have different dimensions for the same item

More screenshots to support answering this question.

ConsolidationScreenshot2.thumb.jpg.2407965260fee905b1654baab4bac32a.jpgConsolidationScreenshot3.thumb.jpg.d4214a7d4a84ad9cfb858eeb0f6cad74.jpg 

Link to comment
Share on other sites

In case that you don't know, a DEFINE is applied to each data set and a COMPUTE to the filtered/oordered data set.

In other words, a DEFINE is applied to each data row of the TABLE FILE where a COMPUTE is applied only to data row of the TABLE FILE once the WHERE clause and the BY field have been "applied".

You cant see a DEFINE as a way to "add" a virtual field that doesn't exist in the source table.

You can perform a WHERE on a DEFINEd and/or COMPUTEd field.

You can perform a COMPUTE on a DEFINEd field.

As for "the system says that the file is unrecognized" you may want to look at the execution log/trace to figure where and why the error.

You can execute your fex with Trace on / Debug on. This option should display from execution button.

A good option to have your request working as you whish is to first JOIN, create the DEFINEd fields and filter (WHERE) the sources tables to then HOLD the result in a new file which then be used to do the calculation.

I strongly suggest that you go step by step.

First, have your raw data.

Second, manipulate the raw data to have the calculation you are looking for.

Link to comment
Share on other sites

OK. Let me start over and see if I am able to do as you've suggested. To clarify, I should perform the join 1st, create a define that is filtered by the exact data I need, then place that define in a HOLD file? Then I am able use those files to perform the computation? BRB to attempt.

Link to comment
Share on other sites

You place ALL source fields that you need in the HOLD file and filter the data as much as you can at first step to limit the number of returned rows. You can also add the virtual fields (DEFINEs) at that step.

After that step, the goal is to work with a single file to perform the more complex calculation and logic.

Working only with desired data set is easier than several table and thousand of rows

Link to comment
Share on other sites

Depend on how much hold data you expect to have.

Generally, I use FOCUS but you will be limited to 2M rows if I remember well which is normally enough.

Advantage to use FOCUS file is that you can create an index on it where you can't on a text (.ftm) or binary file (.ftm).

Also a FOCUS file will generates a .mas (master file) that contains the hold file definition.

Link to comment
Share on other sites

You should not add it to the pre-existing report but start a new report using that HOLD file as the source.

Once you have ran the fex where the output is the FOCUS file, (it's been a while I have not used InfoAssist) you should be able to create another report and the HOLD file should be visible as an available source file. You must select it.

To be able to use it in the pre-existant report, the HOLD file must be physically hold on a physical app path which it doesn't the way you did : it's in "memory" and not available outside the execution. But holding it physically may have several complication to manage that you don't want with the WebFOCUS knowledge you have. Keep it in "memory".

Link to comment
Share on other sites

Oh wow, I was under the impression that I was able to create these hold files and add them to the pre-exisiting report. Since the two hold files I need to build are from two separate tables that are connected at JOIN.

How can I create those two HOLD files and merged them to be used simultaneously in the report I am looking to build?

I apologize if I am pestering you, I'm a student looking to learn as much as I can.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...