Insert newest date

[LasseJ]

Hi all

Can someone help me out how to solve this problem, please?.

I have datefield_1 and datefield_2 which contains different dates.

What I want is the data of datefield_2 to overwrite the data in datefield_1 if it is newer than the data in datefield_1.

Hope my question makes sense :

Regards, Lasse

[mr_vodka]

Could you please explain further why you are trying to do this? is it a one time thing? It seems as tho it would make more sense to create a calc field that returns the higher date rather than overriding data.

[David Jondreau]

Auto Enter a calculation into your Date Field:

Case(datefield1

[LasseJ]

Hi grip

It works just fine. Thank you very much.

Mr_vodka: It is because datefield_1 contains one-time imported dates, which by time will become irrelevant as newer dates are added in datefield_2.

Regards

Lasse

[LasseJ]

Oops...

Just found out, that some of the datefield_1(s) doesnt have any data, and that the script 'grip' kindly showed me, doesnt work in these cases. Does anyone know what to add to the script to make it work so an empty datefield_1 will be overridden by any data from datefield_2?

Regards

Lasse

[Raybaudi]

Case(

IsEmpty ( datefield1 ) or datefield1 < datefield2 ; datefield2 ;

datefield1

)

[LaRetta]

Or even Max ( datefield1 ; datefield2 ) ... assuming they are true date fields.

Another few considerations ... 1) I didn't see it mentioned that, since this is standard date field, it will not update if the value is changed in datefield2 unless (in Auto-Enter tab), it is UNCHECKED to 'Do not Replace Existing Value'. Even though datefield1 is imported (and would trigger auto-enter options if specified), datefield2 will be changed later by Users. And 2) your EXISTING records will not automatically update until either of the date fields are changed unless you force the change. There are various ways to do it. Just place your cursor in datefield1 and Replace Field Contents with datefield1 (after you've set the Auto-Enter Replace). Or you can change datefield1 to a CALCULATION (be sure to specify type date at bottom of calc box). Then back out of field definitions then go back in and change it back to standard date (remembering to re-specify your auto-enter Replace). Regardless of the method you use, back up first.

LaRetta

[LasseJ]

Thank you all!

I chose to do it the way LaRetta suggested, as it seemed the most umcomplicated way to do the script.

It works fine now, and thanks to LaRetta I also got my already imported records, updated as well by Replacing Field Contents

[LasseJ]

Hate to say it, but I'm back..

I still seem to have a problem with auto-updating datefield1, when importing new dates in datefield2.

(Script: Max (datefield2 ; datefield1))

If I import new data to datefield2 (Which is a portal, by the way) it doesnt automatic update datefield1 with the newer date. As LaRetta kindly pointed out, it can be forcely done by "Replace Field Content" when the cursor is in datefield1.

Having that in mind I tried to add such a script to my import-script: Replace Field Contents[Archive::datefield1[""];""]

But! First of all it doesnt work and second it runs through all records. It would of course be nice if it only runs through the records related to the newly imported data.

Does the question makes sense? and can anyone help?

Regards

Lasse

[LaRetta]

It is because datefield_1 contains one-time imported dates

when importing new dates in datefield2.

My suggestion was based upon importing into datefield1 and the Auto-Enter (Replace) being on datefield1.

1) You need to explain 'what doesn't work.' That tells us nothing.

2) You need to explain exactly how/where you are importing and your REAL table and field names. You can't import into a related table - you must be ON the table.

3) What is the match field in the import - is it adding new records or updating?

The value of a recommendation will be in direct proportion to the validity and clarity of the information provided. :wink2:

Edited February 21, 2007 by Guest
Added Item #3

[LasseJ]

OK.

I'll try to see if I can do better:

I have 2 tables. One with records containing data of members; name, adress etc. Its named "Memberarchive". In this table I have a field called "datefield1".

In another table I have the records of membership fees paid. Its named "Membershipfeetable". These data ("datefield2" aka -paymentdate, "amountpayed" and "memberID") are imported from an Excelfile every now and then.

The data from "Membershipfeetable" are viewed in a portal in "Memberarchive" when matching "memberID" of each member.

When making this database I could only import the last paymentdate of each member, so I decided to make "datefield1" for this payment - and make a script who would show the latest payment date of the member; either the once imported data in "datefield1" or a newer paymentdate from "datefield2".

That means: Everytime I import new payment dates in "datefield2", "datefield1" are meant to autoupdate with this newer date. And preferrably in the same script as the Excelfile import is done in.

Hopefully this clarifies my question (and that I havent done everything wrong in the first place

Best regards

Lasse

[comment]

Why don't you simply define a calculation field in Memberarchive =

Last ( Membershipfeetable::paymentdate )

[LaRetta]

Having a date (datefield1) in Memberarchive which shares a commomality with payment dates, points to an inconsistency. If you were converting your data and datefield1 represents the FIRST (or prior) payment for a member, then it should be now moved to the membershipfeestable (if it represents the last payment and no subsequent payments have been imported into membershipfeestable).

And no, you've done nothing wrong, Lasse. But it concerns me when I make a suggestion which can affect someone's business and I'm making suggestions with incorrect or incomplete information. I then feel responsible if something goes wrong. Knowing that one of the date fields is from ANOTHER table makes a lot of difference as well. We'll help you through this ... not to worry.

LaRetta

[LasseJ]

No prob, LaRetta. I dont change anything unless I'm quite sure it works as its supposed to. Dont be nervous. The chance that I'll screw something up my self is way bigger anyway :

You got a point regarding the inconsistency. I'll consider to re-import my old payment dates in datefield2 instead.

Anyways, even with 'comment's new suggestion, I'm still left with the problem that datefield1 is not auto-updated when I import new dates in membershipfee::datefield2...

New ideas, anyone?

Regards,

Lasse

Edited February 26, 2007 by Guest

[LaRetta]

Hi Lasse,

Why do you need to update datefield1? You can use a calculation to display the latest of the two dates, regardless of where they reside (which is what Comment was suggesting). Example, in memberarchive, calculation (result of date) with:

Let ( [color:blue]$last = Last ( YourExistingRelationship::DateField1 ) ; Max ( [color:blue]$last ; DateField2 )

This calculation will always display the latest date of the two. You DO have a solid relationship between the tables, yes? Simply, a table (table1) can't see changes in another table (table2) unless it is an unstored calculation. Keeping two tables synchronized is nearly impossible and prone to break not to mention the redundant data issue. If it is critical that datefield1 be updated when datefield2 is imported, then import again also into datefield1. See the structural problem here?

Update: Corrected blue. I should add that, a few days ago, I had observed that Max ( Last ( Table1::DateField1 ; DateField2 ) ) would NOT work properly. Michael (Comment) forced it to work using a variable. Last() simply won't work within Max() as one would expect. But it WILL work when it is first converted to a variable.

LaRetta

Edited February 26, 2007 by Guest
Added Update

[LasseJ]

Ouch..!

You lost me completely there, LaRetta. You are way out of my league :

I've taken your advice and imported my old paymentdates into datefield2 as you suggested. (That means there are no longer imported dates in datefield1).

And I've tried your latest suggestion, and I cant make work..

I get your point in the structural problem, but my membershipfeetable (where I import my bookkeeping dates (Datefield2)) is a hidden, non-modifiable table, only displayed to others from the portal in Memberarchive. So I thought it would be nice to have an easy-to-see-with-only-one-date field for the last payment date for searching etc.,

But, as time go by and frustration evolves maybe it isn't such a good idea after all.

So heres my last shot at this!:

Since I'm not sure, whether I've made my request totally clear I have attached a FM-database to see a simple version of my database

Anyone to solve my problem?

Best regards

Lasse

auto_update.zip

[mr_vodka]

Can's this be achieved by just having the Membership table sorted by Ascending on Datefield2 in the relationship between the two tables, and then have Datefield1 be a calculation field of Last ( Datefield2 ) and MemberArchive::Amount = Last (Amount) for the corresponding payment amount for that date?

Edited February 27, 2007 by Guest

[LaRetta]

John, if you sort the relationship, you can place the membershipfees fields directly on the layout and achieve the same result (no calculations in Memberarchive required) ... which is what I've done.

Lasse, you will be surprised (and I hope relieved) to find ALL fields gone in MemberArchive except for the ID. Import your payments into Membershipfees and MemberArchive will always display the latest payment date, latest payment amount and latest year - no updating of duplicate fields and no calculations. I've attached your file back.

You will notice that Membership Year is actually the latest Membership Payment DATE but displays just the year. I also corrected your sort script, now sorting based upon your membershipfees table (see your sort order within the script). There is only ONE difference between creating a new field called year and sorting on it - and sorting on a true date and that is ... it wastes adding another field (or calculation). Again, unnecessary because the results are the same.

We were only able to achieve this because you ditched datefield1. Good move. You've just been given a glimpse of the power of relational - things get much simpler and duplicate data disappears. When you want something from a related table then just pull it through your relationship instead of creating duplicate fields (or calculations) in your main table. If questions, just ask.

UPDATE: Also, you have Auto-Enter calculation on DateField2 (in membershipfees) but nothing in it. Your amount in memberships fees is set to 'Always Validate' but there is no requirements indicated on how FM should validate it. You should correct these. And, on your MemberID, uncheck the following: Prohibit modification, Require Value, Unique, and Allow Override. The Developer is the only one with this access (or should be) and they are unnecessary on your unique IDs.

I'm back ... you have allowed access to your MemberID on the layout. It is HERE that you need to prohibit modification. Set Field Control > Behavior to uncheck entry in Browse mode. Disallow - at the field level, ALL payment fields which shouldn't be modified including those portal fields.

LaRetta

Medlemsarkiv_REV.zip

Edited February 27, 2007 by Guest

[mr_vodka]

John, if you sort the relationship, you can place the membershipfees fields directly on the layout and achieve the same result (no calculations in Memberarchive

Hiya LaRetta-

Of course. Slight over look on my behalf. Thanks for pointing it out. :

[LasseJ]

Bingo!

Thanks, thanks and thanks again, LaRetta! (And to others who tried to help me)

It works perfect now - and you're so right about my surprise how uncomplicated its done. It took me a few seconds to understand where "all" my fields from membershiparchive had gone : , but of course...

I'll of course also correct my other "minor" mistakes immediately.

Thanks again - your help has been priceless.

Best regards

Lasse