Portal: how to simultaneously make row changes AND keep track of all recorded rows (history)?

[silf]

Hello.

The portal on the right in the image below records movements of books. Goal: knowing at any time where the books are temporarily located ("dépôt provisoire" means temporary deposit).

Each time the location of a book changes, each time the portal row of this book changes. But I need also to keep track of all movements.

What would be the right approach? I thought I could keep all tracks in the table which my portal is refering too. I tried several scripts so that, when a change in row occurs, another row in the same table keeps the previous record safe. But I failed...

Kind regards.

[bdbd]

You are describing inventory management. This is a system in and of itself. Very quick and dirty…

Inventory management entails tracking where things are and when were things move and where things move to. At a minimum, you need to track individual things (what), locations where things can be placed (where) and movements (what, where from, where to, when, perhaps who, how and why). You might also want to track entities who are responsible for or move things (who).

Moving a thing always entails an entity moving it from one location to another at a point in time. Entity could be a person, a business, etc. Location could be specific, such as a bin on a shelf in a room in a building, or generic, such as "loaned out to an individual". Attaching a reason for movements is wise as it can aid in auditing, tracking lost inventory or other investigative activities.

Based on your original post, you want to know what is and was where. The movement history should contain all this information, as it is suppose to contain what was moved from where to where and when. The last row (or first, depending on sort order) tells you where the thing is — where the thing was last moved to.

Hope this helps.

[Malcolm]

The simplest way to do this is to create a new portal record for each change.

Have the relationship include a reverse sort so that you always see the most recently created record.

In the view that you’ve shown you will see the current record. When you need to see a history you can use a portal that shows more records or used GTRR to display the list in a new window.

[silf]

@bdbd @Malcolm

It seems to me that both of you are proposing quite different ways:

• bdbd: create a new table in which is copied through a script each new last row of table "circulation"

• Malcolm: create a new portal record for each location change (but I do not know how to do that in the right way: the user must think that he is changing the record, not creating a new one each time that there is a change)

Is my understanding correct?

[bdbd]

Sorry for the misunderstanding @silf. I am fairly certain that both @Malcolm and I are suggesting something similar: use a table that documents each change of location for an object. Your circulation table and portal are likely adequate. You now only need to create a new portal row every time the object changes location.

[silf]

Thanks @bdbd
At the time being, when I change e.g. "temporary deposit" value through the portal, the already existing record is changing. I do not know how to convert this behavior into creation of a new record in table.
Direct call for help, I confess.

[silf]

Additional information: I understood that I need one portal row per location change. The challenge for me is to do so that the user only sees one portal row.
Would the solution be a button which would be pressed each time a location change is needed?
Some concrete steps to follow would be welcome if it were not too much asked.

[Malcolm]

1. Set the portal to show one row only
2. set the portal relationship to sort in reverse chronological order.
   A simple way to do this is to have number field that auto-enters a calculation. You can have an auto-increment serial number. Sort Z-A on this field in the relationship definition. I tend to use "Get(RecordID)" because that value is used by the APIs to identify records, so its handy to have around.
3. Use buttons to create a new record. The users hit the button, a new record is created and they can enter data.
4. Lock the fields, and only allow the users to edit it via a card window.
5. You probably want to have a button for corrections and a button for new data.

[silf]

@Malcolm
At your step 3, my script, activated through a button, looks like this:

Go to Portal Row [Select: On; First]
New Record/Request

Wished result: fields only in Portal are emptied and the user can add new datas
Effective result (screenshot below): fields are emptied in and out the Portal

[Malcolm]

In FileMaker everything happens "in context".

• Context
• Context is Everything
• David Attenbruh's World of FileMaker™ - Episode 1 - Context
• Best way to check FileMaker “Context”?
• Layout Context

In your case, everything happened in the context of the current record on the current layout. The first instruction did a lot less that you expected. It went to the first portal row but it remained in the current context. When you told it to make a new record that happened in the current context, not in the related table.

Your script needs to do this:

1. Copy the current record ID into a variable
2. Go to layout based on the related table
3. Create a new record
4. Use the value of the variable to set the foreign key to the establish the relationship
5. Go back to the original layout
6. refresh the portal
7. go to field [ dépôt provisoire ]

[silf]

@Malcolm
Result of my trial to follow your steps:
A new record is created in the related table, the foreign archives_id is added, but then the values added wih help of the portal are not going to the new record in the related table but replacing the values in previous record.

[Malcolm]

Line #3. Not needed
Line #6. Not needed
Line #7. Go to Layout [Original Layout] is safer and easier.
Line #8. Ensure that you have the correct name for the portal object. You may need to refresh the window.

[silf]

Thanks @Malcolm
Unfortunately, it also does not work with your suggestions. Behavior:

1. The new record is created.
2. The archives_id is correctly pasted in the right field.
3. When changing values in the portal, the previous record is changed instead of the new one (also after a window refresh).

Where am I wrong?

[Malcolm]

Ensure the data provided to the new record to create the relationship is correct and sufficient.

[daleallyn]

You received great guidance from @bdbd and @Malcolm (as usual), but I'll offer a bit about how I do this. This "location history" is something that I do in most of my solutions (due to the primary space in which I tend to specialize).

Typically, I have an "Inventory" table which contains the records of inventory items, or library items, or technical lab submissions, etc. I also have a "LocationHistory" table which is related to the "Inventory" table via "LocationHistory::InventoryUUID_fk". This field in the history table is populated at the time of processing a transaction concerning the item in question (book, inventory item, specimen, etc.). The process script handles the LocationHistory transaction when an item is transferred to a new location, technician, sold, returned, etc.

In my solutions, part of the Process Transfer script, or Process Sale script, etc. store $variables for each pertinent field in the LocationHistory table: $iventoryUUID_fk, $transactionType, (and a bunch of security stuff). If there are multiple items to be processed, the $variables which pertain to all equally are set, and then we loop through the line items of the event or transaction to build the appropriate arrays for the history records.

Finally, as we process the transaction(s) we simply open a window to the history table (a blank layout, off screen) and request a new record for each line item. The LocationHistory table is made up of entirely Auto-entered fields for which the $variableName is simply the auto-entry value (plus a few calculation fields sometimes for certain data types). So... Loop > set the $variables which are unique to each line item > new record request... etc. No set field steps are used, just set the $variables for auto-entry to each new record.

Back on the Inventory or Library or Specimen layout there's a portal displaying the LocationHIstory records associated with each inventory record. I typically display it on a Slide Control if space is an issue (often is in my apps). On each portal row there are various merge fields displaying transaction type, date, time, user name, client name, etc. The portal displays the transactions with newest at the top in our cases.

In our solutions users want to see all the history, so the last several rows are displayed and can otherwise be scrolled. @Malcolm mentioned how to display only the current state using a single-row portal.

Hope this is of some help.

[silf]

Thank you @daleallyn for your careful description of your solution.
At first sight, it seems to me that it is a little sophisticated in comparison to what I am trying to do now. It will be certainly useful when I am becoming more advanced.
Unfortunately, my problem described above (March 5) remains not solved despite of @Malcolm's guidance. I suppose it is a very little problem, but I am blocked.
Any idea of solution welcome.
Kind regards.