Weekly Report: Quick Inventory

Hi all,

I thought of this because sometimes I need to check all the items in the top management office, such as whether the laptop, printer, and monitor are working. If any item is not working, I want to mark it and add comments.

Additionally, when selecting a department, I would like to see a field displaying the names related to that department.

If you have any ideas or a sample file to assist with this, please share.

Hello, @Saeed , what you're describing is often referred to as "Business Assets Inventory", or just "Assets" for short (although that can be confused with financial assets if context is not known.

FileMaker provides an Assets starter solution which one could look at for ideas (I don't care for how the starter solutions are done, but there may be some ideas to consider).

In my opinion, the Assets sample example available in FM v17 and v18 (available below the Starter Solutions is a much more useful example (though still far from ideal in my opinion). In fact, in a quick and dirty side project for one of my larger clients we used that sample file as the starting point for the Assets database. We extended it significantly, but it saved a lot time, as it was not a priority of the project.

We added fields, new tables, barcode support for the assets for inventory control, equipment maintenance scheduling and reporting, etc. This version (very different from the "Assets" Starter solution) still has the quirks of the starter/sample development style, but may provide some more ideas for you if you have access to FM 18 or 17.

Normally, we'd roll our own from scratch, but this provided a quick start that we delivered in a couple of days and the auditors loved it (after our modifications and additions). And I believe they're still using it after eight (8) years. :laughing:

Forgot to add that the Sample file includes layouts for iPad and iPhone, so admin staff can walk around the facility and create records, add photos, etc. Saved us even more time not needing to put much time into these layouts. Staff thought we were miracle workers to supply it so quickly. :wink:

Edit: There's also a Personnel Records Sample file in that same group, which may give you ideas or provide a starting point.

Thank you for the clarification, @daleallyn . I appreciate the suggestion regarding the "Business Assets Inventory" or simply "Assets". I understand the potential confusion with financial assets without proper context.

I am currently working on creating a file from scratch that is tailored to suit my environment and requirements. I plan to share it here once it's ready. If you have any ideas for modifications or additions, I would greatly appreciate your input.

Thanks again for the valuable insight!

Hi daleallyn,

I am facing a simple issue with the file I created. The second element, "HR ---- Ahmed," is showing in a separate row instead of being included with the rest.

If you have any ideas or suggestions for improvement, I would greatly appreciate your help.

TO:
image

interface for entry data

the problem:


Weekly Report System.fmp12 (240 KB)

@Saeed , we take a somewhat different approach for assets data entry and reporting (if I'm interpreting your examples correctly). I don't have a copy of my solution developed entirely in-house on this computer (I'm at home today), but miraculously I did found an old copy of the prototype we started with years ago, that I mentioned above.

It's not pretty, but was not part of the contract and just rushed out to help the client as they were starting up.

In our use-case, we (client's staff) entered the asset as the record, then assigned the details, category, location, and if it is assigned to someone, indicate that. List view can be sorted by location, department, person, whatever. Reports are tailored to their audit and management needs. For example, one report uses sub-summary part to sort by employee to indicate everything assigned to each staff member. Another by department, and so on.

In my custom solution, many of the same fields are employed, with more granular reporting, a better UI/UX (IMHO) and other data design preferences, but it accomplishes basically the same thing.

So in our design, there's only one Assets TO, in which most of the details are stored, to whom it's assigned, it's expected location, etc. It's actually a very simple file with only 7 tables. It probably has a couple more as delivered (I don't have the final version here), but not much to it. Maybe it'll give you an idea of an approach to consider.

Again, this was just a rushed extension of the Sample file provided in FMPA 17/18.


1 Like

I said: > It's actually a very simple file with only 7 tables.

To clarify, there are only three tables that are the basics for asset tracking; Assets, AssetPhotos, and History (residual from the sample file, but useful). A Locations table for use as Value List, and I don't recall how we managed staff names, either a local table or a relationship to an HR file.

The other three tables of the "7" are for the Maintenance stuff.

I'm reminded that the original "sample" file included the five (5) image containers in the Assets table, which is an approach I avoid (file_1, file_2, etc.), so the AssetsPhotos table was added and related to the asset by UUID.

We have an "AcquisitionDocs" table in our production solution, and additional tables for any other related "child" documents and such.

Overall, a very simple design.

1 Like

@daleallyn You consistently bring innovative ideas and valuable support, which I greatly appreciate. Additionally, I have learned many new concepts directly from you. However, I am currently facing some issues with the portal and list, as shown above.

If you have any suggestions or solutions to address these issues, I would be grateful for your assistance.

Thank you once again for your ongoing support and expertise.

@Saeed , sorry if I was not clear. I suggest you rethink your data structure if you can. This will depend on whether this is integrated directly within a larger solution, or if Assets are largely managed in a stand alone file (or together with HR, which is not unusual).

If you have a table named Assets as you do, and you have fields for all the pertinent data collected, including which department to which it is assigned, you eliminate the need for the "Department" and "Assets2" tables/TOs.

I think I'm being confused by your UI/UX and workflow. It appears to be contrary to business models I've encountered, but that doesn't mean it's wrong. We may not have enough info to discern correctly, and/or I'm just not grasping your structure.

Normally, for corporate assets management we start with assets, but I can see that someone may start from Department and make the Assets the "child" of the Department table. That's not what my clients do (nor my business approach is), but that's fine. There's typically more than one way to get where one needs to arrive.

One could start with just Department and Assets tables, setting it such that Assets is the "Child" of Department (i.e. one department can have many assets); adding a portal to assets and enter assets. If each asset is assigned to a staff member within the Department, one could then create a Report to display what is under each department and each employee withing the department. Does that make sense? A portal isn't going work in this setting, but a Report using a sub-summary part will do nicely.

However, one could also structure a JOIN table between Department and Assets in a "many-to-many" relationship and view the details using that TO. A problem in this case would be that if you aren't "assigning" an asset to person in the Assets table (Assets::assignedTo), it gets messy again. Again, the structure isn't quite right for this. In this case one might have a Staff table and a Department table; then a JOIN table ('AssetsAssignedByDept) that store the UUID of Staff and Department in it; finally defining the Assets in the JOIN table. Again, not ideal in my opinion.

You have a "many-to-many" relationship b/n Department and Assets without a Join table which stores a foreign key from each. I believe this is the source of your problem.

Upon which table occurrence are you basing the two screenshots you shared? Perhaps that will help shed some light.

I don't intend to be torturing you, I'm just struggling to grasp your data design. If you want to work from Department to show assets, if we simplify to just the two tables as Parent--<Child, and create a report you'll have exactly what I think you're seeking. Create the Asset record, assign it to the staff member, run the report. The report can be onscreen and a printable version layout. The structure as you have won't return accurately.

1 Like

@Saeed , here is a screenshot of a file I just created (quick and dirty). It may illustrate you one approach we might take for what you describe. This file is very simple: Departments --< Assets. Assets were entered into a portal on the Department form layout, using asset name, category, condition, and assignedTo.

Then I made a simple report (for screen viewing, I'd do a separate layout for PDF or print). There are four (4) layout parts: Header, Sub-summary when sorted on department title; Sub-summary when sorted on Assets::assignedTo; Body, (could add a footer, or other summary parts). I did not "beautify" it.

The data entry can be done differently where the assignment is applied via script, etc.

Since your title is "Weekly Report:..." , suggesting a "report" rather than a form with portal(s) on it. Does this get you closer? It could be implemented using the input screenshot you provide by scripting or auto-enter calculation for the assignee of the asset.

Screen Shot 2024-06-19 at 5.36.04 PM

1 Like

I apologize for the delay in responding. I had some personal matters to attend to yesterday.

Regarding your question, yes, the screenshot above reflects what I need. However, is data entry for the asset done via a portal?

My aim for this app is to have the technical team conduct weekly checks on all assets in the Top Management office to ensure everything is working correctly. They should then report their findings to the line manager.

To streamline this process and move away from the old paper-based system, I would like to implement this on an iPad.

Thank you for your assistance.

Good morning, @Saeed . No worries (ever) about reply timings. This is an asynchronous kitchen. :wink:

In my example, yes, I entered the Asset details via a portal (attempting to guess your needs). I did not go to the trouble to filter the portal by staff member as you illustrate, as it was off the main focus of the report. That's just a UI issue. If that is really needed, I'd probably use a "popup picker" to set a global (in my Globals table) to set the filter on the portal and to set an auto-enter calculation to the asset entry.

Here's the screenshot of the very simple form I used to create the report. The main thing about the report is setting up the Sub-summary parts and sorting on both the Department name and the assignedTo (or whatever you call it) field.

So a button might call a script to go to the report layout and sort the records. From that layout, one could generate a PDF or send to print (on a properly designed print layout). Of course, there could also be a Find to create a found set by department if needed.

If I wasn't clear, the report layout is based on the Assets TO.

1 Like

I should add: If you are wanting to have a historical record of asset conditions, such that tracks for each week, the process would be a bit different.

For this approach, you'd have an Assets table and something like AssetsHistory. Then your condition, location, etc. would be entered to the history table, and the reports would be based on that.

This is another reason why I prefer a company-wide Assets table, and an AssetsHistory table with assignments applied there (including department, location, to whom each is assigned, current condition, and so on). It's scalable and flexible.

It just depends on one's needs. :slight_smile:

I appreciate your solution and support. Today, I learned a new technical aspect that, although simple, adds value to my experience with FileMaker.

If you could assist me further as mentioned in my initial post, I would greatly appreciate it. Here’s the scenario:

First Table:

  • Department
  • Assigned To

Second Table:

  • Department
  • Assigned To
  • Asset
  • Status (working or not working)
  • Comment

First Layout:

  • Select the department, then display related assignments. ( Conditional Value List)
  • Below, include a portal with the following fields: Asset, Status, and Comment.

Weekly Report:

  • Display the report by department.

This setup will help me create a system that suits my environment. Additionally, this will be a new technical skill for me, enhancing my experience with FileMaker.

Thank you for your assistance.

Hi @daleallyn ,I understand you're busy with other work, but I wanted to share this information for the benefit of anyone else encountering the same issue.

The attached screenshot shows that the solution works perfectly. However, I'm facing an issue where, if the portal contains more than one record, it only displays one record and ignores the others.

Thank you for your time and assistance.

TO:

portal:

report:

If I understand what you want, @Saeed , it can be achieved even simpler. There are several ways to deliver this using different UI/UX methods , but one way is as follows (without additional tables or TOs).

Needed:

  • A Globals table with one record only, all fields use global storage (I have one in every soluiton)
    ** New field: g_deptName
    ** New field: g_staffName

  • Value lists:
    ** Departments -- based on the field Departments::deptName (or whatever you call it)
    ** Staff by Department -- based on the Assets::assignedTo field (see screenshot)

  • Fields on layout as popup menus for each of the Globals fields mentioned above, using the proper value list for each.

  • Script triggers on each popup OnObjectModify
    ** The script trigger on the department selector popup is a simple find for department by name and clear the g_staffName field, and refresh the port
    ** The script trigger on the Staff Name selector is just a refresh portal

  • A filter on the portal: Assets::assignedTo = Globals::g_staffName

Let's say we're sitting on a layout based on Departments. Select the department from the popup menu described; the portal will be empty. Select the staff name and the portal will populate with only assets assigned to them.

One would would probably want to clear the two Globals fields OnLayoutEnter.

In a large organization with many assets, departments and staff, I would not use this approach, but it does deliver what I think you want and what I believe your screen shot is illustrating.

The actual UI/UX elements can be designed to taste, but this describes one simple process for display. If adding new assets and assigning to a staff member, one would need to tweak this, and/or use a different layout, etc.

Value list for staff selector:

If this is what you want to do (or extend it) I can cleanup the file and send it. It's pretty messy as it is. :blush:

Edit to clarify: I was creating my reply above as time allowed, so did not see your newest post/reply. So my reply may be a bit off target.

Edit 2: You get a single record because of your many-to-many relationship. It will only show the first matching record. You'd need a join table and change context if using this approach.

Edit 3: Here's the little file. Maybe it'll have something that will help you. It's much simpler than what your relationship graph indicates, and has no historical table for weekly data. Easy to add.

AssetsTestSaeed.fmp12 (532 KB)

1 Like