FINDSET and Rename

JDVyskaJDVyska Member Posts: 179
I have a client project I'm working on where they need a custom table merged to multicompany, but each company has records in it. So, I'm simply going to add an offset # per company, say 100 per each company.

So, the example data is simple, ID is the only PK field, an integer:
Company A:
ID        | Some text
1          | Text
2          | Text
3          | Text

Company B:
ID        | Some text
1          | Text
2          | Text
3          | Text

The result should be roughly:
Company A:
ID        | Some text
1          | Text
2          | Text
3          | Text
101        | Text
102        | Text
103        | Text

I was thinking I'd be slick and use FINDSET(TRUE,TRUE), since that allows modification, even on the key you're using.

Turns out, FINDSET can't quite be used that way. Company B's 1 becomes 101, then it's at the end of the dataset and stops.

In my case, it's easy to iterate like:
Offset := 100;  // in real code, I'd bump this up per company
IF Custom.FINDFIRST THEN REPEAT
  Custom.RENAME(Custom.ID+Offset);
  IF Custom.FINDFIRST THEN;
UNTIL Custom.ID >= Offset;

Just thought I'd share this, since I misunderstood FINDSET's TRUE,TRUE combination. If I can, someone else will also eventually and come here. :D



Edit: Yes, I know most of the time you'd be better off copying to a temp buffer, renaming, copying back, or even insert/deletes, but in the actual details of my situation, not the simplicity of the above example, I have a host of tablerelations to this master table, so I want NAV to cascade the RENAME for me.
JEREMY VYSKA
CEO, Spare Brained Ideas, Göteborg, Sweden
New (April 2021) Getting Started with Microsoft Dynamics 365 Business Central Book Available: "Your First 20 Hours with Business Central"

Answers

  • BeliasBelias Member Posts: 2,998
    basically, you were doing a so called "next from hell":
    ideally, your loop should look like this
    Offset := 100;  // in real code, I'd bump this up per company
    IF Custom.FINDSET(TRUE,TRUE) THEN REPEAT  // Aaargh!!!use findset, not findfirst with loops!!!
      Custom2 := Custom;
      Custom2.RENAME(Custom.ID+Offset);
    UNTIL Custom.ID >= Offset;
    

    the parameters of the findset function just changes some parameters of the query sent to sql (isolation level for locking purposes), it does not allow you to do avoid a next from hell issue.
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • jversusjjversusj Member Posts: 489
    what about filtering one record set, repeat until, and in that loop, get another instance of the record and rename that?
    kind of fell into this...
  • JDVyskaJDVyska Member Posts: 179
    Belias wrote:
    basically, you were doing a so called "next from hell":
    ... snip ...
    Belias wrote:
    the parameters of the findset function just changes some parameters of the query sent to sql (isolation level for locking purposes), it does not allow you to do avoid a next from hell issue.

    Yuppers. It's a very crazy project I'm working on, so there's no avoiding ugly 100%. I figured if I could fall into the "next from hell" trap thinking FINDSET would magically save me, it was bound to catch someone else.
    JEREMY VYSKA
    CEO, Spare Brained Ideas, Göteborg, Sweden
    New (April 2021) Getting Started with Microsoft Dynamics 365 Business Central Book Available: "Your First 20 Hours with Business Central"
Sign In or Register to comment.