Navigating through a related file with script

[djlane]

Hi,

I need some help please in navigating through a related file using a script.

I have 2 files, A and B. File A contains fields A::Available and A::Allocated. File B contains fields B::Available and B::Allocated.

I need to allocate every qty in A::Available across one or more value of B::Available related records in B.

eg.

A::Available in the current record in A has a value of 200. In B there are three related records with B:Available of 140, 50, and 300. I need to allocate the value in A so that the values when finished are:

A::Available = 0, A::Allocated = 200

record_1 B::Available = 0, B::Allocated = 140

record_2 B::Available = 0, B::Allocated = 50

record_3 B::Available = 290, B::Allocated = 10

The process should continue until A::available is 0, and then go to the next record in A and start again.

I can't figure out how to navigate through the records in B. Any help would be really appreciated.

Thanks!

[comment]

The rules are not clear enough: why does all the quantity from A go to record_3, while the other records get nothing?

Overall, this doesn't seem to be a good method: at the end of this process, you have no trail of what has happened, and no way to go back. Somewhere there should be a record for each allocation made by the script.

In general, you can navigate through related records by Go to Related Record [show related only] first. Then you navigate through the found set in the child table.

BTW, you will get better responses by describing the real situation, rather than an abstract one.

[djlane]

Thanks veteran,

At your suggestion I will describe the real situation.

We import and distribute shoe boxes for adidas factories in Indonesia. We operate a bonded warehouse so that all boxes are imported by us and delivered to the adidas factories tax and duty free.

As we are a bonded facility, Customs regulations require that, for every outgoing delivery, we identify on an item by item basis the container in which the item entered our warehouse. It does not have to be the exact container, but it must be a container which contains the item number that is being delivered. The file which contains the contents of incoming containers is file B in my hypothetical example.

File A in my hypothetical example is the delivery details (line item) file. For every line item we deliver, we have to identify the incoming container (ie any container which contains that particular item number).

So, my script is to read through the delivery line items file, and then choose which incoming container(s) to allocate each delivery to (on an oldest container first basis).

To answer your comments about my initial posting, my example shows a delivery which is allocated over three containers. Only a qty of 10 goes to the last container, not the full qty as you have said?

With regard to having a record of what happened, I have a third file (an "allocation table") which stores the details of which items have been allocated to which containers. The script creates records in this third file.

Thanks for your help!

[comment]

OK, but I am having trouble matching the reality to the tables and fields you have described. I see 4 basic tables here:

In (incoming containers)

InItems (incoming items) - contains ProductID and Qty

Out (outgoing shipments)

OutItems (outgoing items) - contains ProductID and Qty

Now, if I understand correctly, we want a 5th table to link between InItems and OutItems, so that for each OutItem there's one or more record indicating the source InItems? I don't see Available or Allocated anywhere, except as a calculation.

[djlane]

Hi,

Yes there are 5 tables as you have described, the 5th "Allocation Table" being the table which allocates outgoing shipments to incoming containers.

The line item tables contain many fields (if that is your question?), including ProductID and qty.

In the incoming containers table (line item), there are three fields relating to the alocation process. Incoming_qty, qty_allocated, and qty_available. qty_allocated is a calculation field (sum of qty allocated of related records in the allocation table), qty_available is also a calculation field (incoming_qty less qty_allocated).

In the outgoing shipments line item file there are qty_delivered and qty_allocated and Qty_remaining. Qty_allocated is a calculation being the sum of qty_allocated in the related records in the allocation table, qty_remaining is qty_delivered minus qty_allocated.

I still have the navigation issue. If I go to related record, then go to record(next), what happens is that it goes to the next record in the current table instead of the related table.

thanks!

[comment]

I am with you (I think) regarding the incoming. I am not sure why you need the remaining calc in the outgoing. I'd think you need only to check that the sum of allocated is equal to the qty shipped out.

In any case, coming back to the navigation: when you leave a layout to go another, then come back, you come back to the same record you've left. To take a simple example:

Go to Layout [Parent]

Show All Records

Go to Record [First]

Loop

# DO SOMETHING WITH PARENT

Go to Related Record [Child ; Show only related ]

Loop

# DO SOMETHING WITH CHILD

Go to Record [Next ; Exit after last ]

End Loop

Go to Layout [ Parent]

Go to Record [Next ; Exit after last ]

End Loop

This loops between all parents on the outer loop, and each parent's children on the inner loop.

If I go to related record, then go to record(next), what happens is that it goes to the next record in the current table instead of the related table.

This can only happen if there are no related records. In such case you stay on the current layout. You can check for the existence of related records by:

If [ Child::ChildID ]

Go to Related Record [Child ]

Else

# DO SOMETHING ELSE

End If

[djlane]

Yes you are correct, the remaining balance calc on the outgoing was used when tha allocation process was done manually. It sets a flag to warn that there is a remaining balance.

I stll have the navigation problem. Take a look at the attached sceenprint. I created a small script in the delivery detail file to show you what I mean.

As you can see, the related (child) file (OTW details - the incoming containers) is still on the first record, while its the parent file has moved to the second record.

In my real script, even when I do manage to get the child file to move to the next record (either by calling a script in the child file which contains "go to next record", or by omitting the first record), when I try to extract data from that record, it still picks up data from the first record.

If you see the attached "screenshot2" you can see the delivery detail file in the middle, with a qty to allocate of 12,000. In the top file (OTW details) you can see that the first record has 3000 available, and 3000 has been allocated to that record, leaving a balance of 9000 to allocate. A record has been created in the allocation table at the bottom showing 3000 allocated.

The data is moved from the OTW details file to the allocation table by using global fields in the Delivery Detail file. You can see these global fields just above the script debugger window.

The script then moved to the second record in the OTW detail file (you can see the second record is highlighted) . However, when I set the global fields the data is coming not from the second record but from the first. You can see the first global field contains the BC23 number (thats the name of a Customs document) of 45678, instead of BC88777665.

Any ideas ??

[comment]

It's difficult for me to form a clear picture of what you have. I'd suggest you try this with a simple file first. It's also possible that this has something to do with the tables being in separate files (?). I'd start with a single file containing all the required tables. After you have that working, separate the files, and see how that affects the process.

The only real hint I see is this:

when I set the global fields the data is coming not from the second record but from the first.

That would indicate you're not in that table, and you are fetching data thru a relationship instead.

[djlane]

Hi Michael,

I can't really put all the tables in one file - I converted from FM6 halfway during the development.

My problem seems to be twofold

1. Making the related file "current" (as you said - I'm not "in" the table) so that I can navigate from record to record, and

2. After navigating to a different record, to "refresh" the relationship so that FM knows which of the related records is now the current active record.

Is there some script step that I'm missing that can achieve this ?

cheers

david

[comment]

You can consolidate your files by importing all tables into a single file (each time to a new table). A lot of work cleaning it up afterwards, but it's possible.

In the current situation, do you have layouts in your main file for the related tables in other files?

I don't have much experience working with separate files, but I think that's all you need. Once you go to a layout belonging to a related table, you are "there", regardless of the file.

I didn't understand your second question: FM knows which records are related. FM doesn't know nor care which of the related records is active. If you reference a related record, it will always be the FIRST related record (in the relationship's sort order).

[djlane]

Yes I could place all the tables in one file, but as you said there would be a lot of tidying up to do, and from what I have read on this forum, its not really worth the effort.

You have hit the nail on the head when you say that FM neither knows nor cares which of the related records is active. Thats exactly my problem. FM seems to take the value only from the first related record, even if I have managed to navigate to the second related record.

I appear to have solved the problem by placing a portal on my layout, and using go to related recod, then go to portal row. I can navigate within the portal freely, extracting data from whichever related record is active, and creating records in my allocation table with that data.

Thanks a lot for your help, much appreciated.

[djlane]

Yes I could place all the tables in one file, but as you said there would be a lot of tidying up to do, and from what I have read on this forum, its not really worth the effort.

You have hit the nail on the head when you say that FM neither knows nor cares which of the related records is active. Thats exactly my problem. FM seems to take the value only from the first related record, even if I have managed to navigate to the second related record.

I appear to have solved the problem by placing a portal on my layout, and using go to related record, then go to portal row. I can navigate within the portal freely, extracting data from whichever related record is active, and creating records in my allocation table with that data.

Thanks a lot for your help, much appreciated.

[comment]

Going to a portal row is about the same as going to the related record itself - except more complicated.

FM seems to take the value only from the first related record, even if I have managed to navigate to the second related record.

That's not very well put. The value depends on where you are at the moment.

For example, let's take a structure Parent > Child > Grandchild, and a reference Child::Value:

- If you are on a Parent layout, Child::Value is the value from the first related Child of the current Parent;

- If you are on a Child layout, Child::Value is the value from the current Child;

- If you are on a Grandchild layout, Child::Value is the value from the current Grandchild's parent Child (actually, it's from the first related record in Child, but since a Grandchild has only one related record in Child, it amounts to the same thing).

In short, a reference SomeTO::Value refers to:

- If you are on a layout that belongs to SomeTO - the value of the current record;

- If you are on a layout that belongs to AnotherTO - the value of the first related record in SomeTO (evaluated from the context of the current record in AnotherTO).

[bruceR]

Maybe what's missing is just a Select Window [current window] statement after GTRR.

[djlane]

That's not very well put. The value depends on where you are at the moment.

For example, let's take a structure Parent > Child > Grandchild, and a reference Child::Value:

- If you are on a Parent layout, Child::Value is the value from the first related Child of the current Parent;

That is the issue. the value of the child record is the value of the FIRST related record. I assumed that if I navigated to the second record that that the parent would then see the data in the second record.

This is what happened

My script in the parent needed to navigate through the related records, but when I used go to related record / go to next record, it would navigate to the next record in the parent file instead of the related file. My question was how to make the related file the active file so that navigation in the child file was possible. So, I created a one line script in the child file go to next record. This worked - the second record would now be shown as the active record in the child file. But FM still took the data from the first record only. Back to square one.

Using go to portal row instead has made the navigation I need possible. However, Bruce seems to have supplied the missing script step that I needed. Select Window [current window] is the step that tells FM which file to make the active file.

Thanks guys