How to make timestamp field show data for non-related records

[jeffamm]

I have a main table of products with skus, and a related table (matched on sku) of data from eBay with listing information about the sku.  When I update the related table I show all records and delete them, then re-import with the latest download from eBay.  That all works fine.  I added a timestamp to the related table so I'd know how recent the last download was.  In the main table I display some data from the related table, such as price, and I have a field next to it showing the timestamp from the related table.  My problem is that if the related table has no data for that particular sku, the timestamp does not display (because there's no record matching the sku in the related table).  But knowing the timestamp is still necessary (as in, as of the timestamp date the listing is missing from eBay). So I'm trying to make the timestamp display for all the records, kind of like a global field, but I haven't found anything that works for this.  Anyone know how to do it?  Thanks in advance and Happy 4th!

[comment]

If all related records have the same timestamp, then there is no point of having the timestamp field in that table. Place it in the parent Products table and make the script that updates the related table update this field too.

This is assuming you update the related table for each product separately. Otherwise, there is no point in having the same timestamp replicated in all the product records. Place it in a single-record Preferences table instead.

 

[jeffamm]

Yes all related records have the same timestamp.  The related table is update for all products in one import.  I don't use a script to update the tables, I just manually download them (this is a one-user system).  

[comment]

If you do not script any part of this process, then you will need to update the timestamp field manually too. Because - as you have found out - if the process ends up with no records being imported into the related table, you have nothing that would hold the timestamp of the import attempt.

I don't see why you wouldn't script this, at least in part.

 

15 minutes ago, jeffamm said:

this is a one-user system

Then you can use a global field, in any table.

 

[jeffamm]

Maybe I'm not describing it correctly.  The import always has records because it contains all of our eBay listings, usually 3500.  But eBay ends listings from time to time, so when we look at the record for a sku where the listing was ended, the timestamp doesn't show up, but we want it to.  Regarding scripting, I've just never developed the skill with scripts so always try to avoid them.  We don't have to manually update the timestamp because we delete all the records and import the latest download from eBay, so the table always gets fresh records and a new creation timestamp.

 

Edited July 4, 2020 by jeffamm

[comment]

2 minutes ago, jeffamm said:

The import always has records

Then perhaps you could simply change the timestamp field in the related table to a global field, auto-entering creation timestamp. Then the field will update for all records anytime you create or import a new record. 

It should be stressed again that this will work only for as long as the solution is not hosted. 

 

[jeffamm]

When you say we can use a global field , I have tried this in the past and not been successful.  I will try again if you think it should work.

 

Yes this just runs on my desktop, it is not hosted.

Hey, that works!  Huzzah!  Funny that it has not worked for me in the past, I can't think of what I was doing differently.  Many thanks for your assistance.

[comment]

19 minutes ago, jeffamm said:

Funny that it has not worked for me in the past, I can't think of what I was doing differently. 

Hmm, you're the second person to tell me the same thing in the last 12 hours. So it must be some magic touch that I have ...😉