Is it possible to use a variable to specify which fields to update in a table with the update_recordSet?
I have a table with sixty different fields in which all records in specific fields will need to be cleared out each month, but each month, different fields will need to be cleared out. Can I use a variable to specify which fields?
Something along the lines of:
str fieldName
fieldName = ‘JanuaryOnlineSales’;
update_recordSet myTable
setting
fieldName = 0;
Is there any way to make something like this work? It’s not recognizing my field name. I’ve tried various field conversion functions, but nothings worked so far. Any suggestions?
No, it’s not possible - unless you improve your data model. If you normalize your table, you will be able to write code like this:
update_recordset myTable
setting OnlineSales = 0
where myTable.Month = MonthsOfYear::January;
If you wanted to build a query with columns selected by business logic, use the Query framework instead of queries hard-coded in X++.
I appreciate the response. Unfortunately, I don’t think that type of normalization is going to be possible. This is a table to track an entire year’s worth of sales for each month, for each item, for each type of sale. I have 100,000 items to track each month, in 7 different types of sales categories (online, wholesale-west, wholesale-east, etc.). To track this, I have a record for each item and fields for each month/category: JanuaryOnline, FebruaryOnline, JanuaryWholesaleWest, FebruaryWholesaleWest, etc.
This table is used for reporting purposes. A batch job runs each month to populate the previous month’s sales figures so reporting can be accomplished for each item/sales category. Querying the transactional data each time we run the report isn’t practical since it will be in the tens of millions of records. Running a monthly batch job to populate the table and then querying the table will give us the sales figures we need very quickly. But it’s a lot of fields.
7 categories x 12 months = 84 fields total
But only 7 of those fields need to be updated each month, hence the update-recordSet to reset those 7 fields before repopulating with this year’s data. Since my actual fields change each month, a variable would be preferred. But instead, I can just update the fields in a switch/case statement. A few more lines of semi repetitive code, but accomplishes what I need.
Unless you have any better ideas, thanks for your help. It works as is, I was just hoping to pare down the code if possible.
The normalization definitely is possible - it’s exactly what relation databases are optimized for. Are you aware of analytic and data warehouse databases that are kept separated from OLTP databases? You might, for example, want to build an OLAP cube from your relational table.
If you want to build the query in X++, use the Query framework, as I already said.