1 2 Previous Next 16 Replies Latest reply on Jun 12, 2017 11:48 AM by gofmp15

    Field data disappears when switching layouts on same table.

    AitchB

      Hi,

       

      I have a problem that I cannot solve. I hope someone can point me in the right direction.

       

      I have 3 tables: Clients / Bookings / Cashbook

       

      Relationships:

      1. Clients::name_full  (related to)  Bookings::name_full ( A booking can be cancelled but a client remains)

       

      2. Bookings::date_arrive  (related to)  Cashbook::date_arrive.

       

      A booking can be deleted (option not taken up) but if client cancels, deposit is forfeited and the booking is marked no show.  The cashbook entry can’t be cancelled.

       

       

       

      The problem is that the bookings::date_arrive, entered on layout booking, disappears from layout planning, replaced by a ? and when I return to layout booking it has also disappeared.

       

      This also happens with the 2nd relationship deleted.

      The field: date_depart does not change!

      It’s not the field size.

      They are both « date » fields.

      The date_arrive on booking layout and planning layout are the same field!

       

      I think the 2nd relationship is not required and I should use an « if booking::date_arrive = cashbook::date_arrive; show cashbook::payment amount; 0 » ?

       

      Thanks in advance,

      Henry

       

        

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

        

       

       

       

       

        • 1. Re: Field data disappears when switching layouts on same table.
          greatgrey

          The question mark often means the field is to small to display the data as formatted.

          On the relationship you need to be careful of what you let it delete. i.e. does it deleted related records?

          • 2. Re: Field data disappears when switching layouts on same table.
            philmodjunk

            Beyond that describing your layouts and relationships in more detail would be helpful.

             

            Your relationships by the way, look problematic:

             

            1. Clients::name_full  (related to)  Bookings::name_full ( A booking can be cancelled but a client remains)

             

            And what do you do if two clients have the same name?

            What do you do if a client changes their name?

            What do you do if you accidentally misspell a clients name and don't discover the error until after you have add related records in bookings?

             

            This is why you should link the tables by an auto-entered ClientID that has nothing to do with the client's name. Then name issues do not keep your relationships from working.

             

            2. Bookings::date_arrive  (related to)  Cashbook::date_arrive.

            And what happens if two different bookings have the same "date_arrive" value?

             

            Again, an auto-entered BookingID would be a better method for linking these two tables.

            • 3. Re: Field data disappears when switching layouts on same table.
              BruceRobertson

              Adding a practical example to Phil's point number two:

              Let's suppose that on the same date there is a wedding booked by one client; and a memorial service booked by another client.

              Both clients will see booking details, guest lists, and expenses for both events.

              ( of course we do not know what kind of bookings you create )

              • 4. Re: Field data disappears when switching layouts on same table.
                AitchB

                Phil,  Bruce and greatgrey, thanks.

                 

                If I make a relationship (for the payments) id_bookings to id_cashbook, how or where do I say "if arrival date on a cashbook table payment is the same as an arrival date on bookings table, pop it here on the appropriate portal field on the booking layout? 

                 

                Does it matter if, when making a relationship, if you drag cashbook_id to booking_id first or should it be booking_id to cashbook_id?

                 

                I attach the relationship tables.Screen Shot 2017-06-09 at 14.44.51.png

                 

                I have made 2 new fields for the arrival dates: date_arrive_if_rental (on cashbook table) and date_arrive_new (on the booking table) in case the old fields were messed up.

                 

                It was working before the dates stared disappearing!! I think what happened is on  adding fields in the field picker I inadvertently changed another field. I noticed that after entering a new field, the cursor switches to some other field in field picker and I missed that, changed the other field and probably messed up the DB!

                 

                A bit of info...

                The booking is a reservation for our villa. The villa can be occupied by 1 to 4 people but I just use one name for the whole group, which I call “client“.

                 

                It doesn't matter if the client's name changes as it is only changed on the client table. The booking table's "client" name will change automatically but has no effect on the booking. id to id I will now use but this is not affecting the DB.

                 

                However each booking has a unique arrival date.  Only one client (group) can occupy the villa at the same time and only one client (group) can have the same arrival date.

                 

                If a payment is for a rental, I enter the arrival date on the cashbook table entry. All other entries will not have an arrival date. It's left blank. On the booking layout I have a portal that picks up all cashbook payments with the same arrival date as a booking.

                 

                The info on the FMP DB is for our use only. I produce a planning (the bookings in list view) and send that to our housekeeper and manager. No client sees the booking details. The only calendar they see is on our website, which is exported from AirBnb's website or through the agency used.  FYI here is the villa.

                 

                It was working before the dates stared disappearing!! I think what happened is on adding fields in the field picker I inadvertently changed another field. I noticed that after entering a new field, the cursor switches to some other field in field picker and I missed that, changed the other field and probably messed up the DB!

                • 5. Re: Field data disappears when switching layouts on same table.
                  philmodjunk

                  Your relationship between booking and cashbook is wrong and won't work. You need:

                   

                  bokking::ID_booking = cashbook::fkID_booking

                   

                  this this requires adding a new field to cashbook and populating it with the correct matching data.

                   

                  "It doesn't matter if the client's name changes as it is only changed on the client table. The booking table's "client" name will change automatically but has no effect on the booking. id to id I will now use but this is not affecting the DB."

                   

                  This is not correct in the relationships shown. A name change will disconnect customer and booking records from each other. Duplicate names, on the other hand, will connect bookings records for two customers to the same customer record.

                  • 6. Re: Field data disappears when switching layouts on same table.
                    AitchB

                    Thanks for that. Will try.

                    1 of 1 people found this helpful
                    • 7. Re: Field data disappears when switching layouts on same table.
                      AitchB

                      That was a great help. Seems to be working. Just a few things to test and check.  Thanks again.  Henry

                      • 8. Re: Field data disappears when switching layouts on same table.
                        AitchB

                        Happy sunny Saturday to you all,

                         

                         

                        The date problem has been corrected, Thanks for the pointer. The rest I explained badly as I am not an expert by any means (call me an interested beginner).

                         

                         

                        The tables: Clients, Suppliers, Banks and Agencies, did not need to be related as I get the info required on the Cashbook and Booking from drop down lists. I intend to put them all in one « Contacts » table under types, but later. That works fine.

                         

                         

                        On the BOOKING layout I have the client name pulled from a ddlist from Clients::name, the booking details and then I have a Portal to show payments for that booking.

                         

                         

                        On the CASHBOOK table, for a payment received, I enter the (unique) arrive_date (if it’s a rental, otherwise it’s blank), the invoice date and the paid amount. There can be several payments for the same booking. Deposit, final payment, extra payment…

                         

                         

                        Then this is what I cant see how to do. Can you help?

                         

                         

                        For any booking, if the booking::arrival_date = cashbook::arrival_date I want to post that cashbook::invoice_date and ::paid_amount in the portal.  So there can be several payments for the same booking.

                         

                         

                        I presume the Cashbook::_fkID_booking is propagated with a record number and is auto when fully working? Screen Shot  Relationships.png

                        • 9. Re: Field data disappears when switching layouts on same table.
                          philmodjunk

                          If ID_Booking auto-enters a serial number or Get ( UUID ) and you enable "allow creation of records" for cashbook in this relationship, then you can use a portal to cashbook on a bookings layout to record payments with the matching fk field automatically updating to get a matching value. Other options, some of whom use a script to set the fk field are also possible.

                           

                          You really need a link between contacts and bookings that uses a contact ID instead of a name. As an example, say your villa suffers a major plumbing problem and you thus need to contact the client, John Smith, who had booked the place for tomorrow. You don't want to find that you have three contacts named John Smith and not know which to contact.

                          • 10. Re: Field data disappears when switching layouts on same table.
                            AitchB

                            Philmodjunk you are quite amazing, I do not know how you find the time and

                            have the patience with this. I congratulate you!!

                             

                            I have produced a ripped down version of what I am trying to create with

                            notes on the layouts. I have added the main fields and added several _ids

                            and _fks (probably some are not needed). I have purposefully not made the

                            relationships as I am a bit lost.

                             

                            Your 1st paragraph: Id_booking relates to what field in cashbook?

                             

                            Your 2nd paragraph: As we use the arrival date because it is unique

                            (no-other client/group can arrive on the same date). Several clients have

                            repeated over the years (one has come here 15 years on the trot!!). their

                            arrival dates obviously change and that's why we work with arrival dates.

                            Also makes a planning list in arrival date order more simple to produce.

                             

                            Can you tell from the db what I need to do (silly question really)

                             

                            Thanks in advance,

                             

                            Henry

                            • 11. Re: Field data disappears when switching layouts on same table.
                              philmodjunk

                              Your 1st paragraph: Id_booking relates to what field in cashbook?

                               

                              From your most frequent screen shot, Cashbook::_fkIDBooking

                               

                              Your 2nd paragraph: As we use the arrival date because it is unique

                              (no-other client/group can arrive on the same date). Several clients have

                              repeated over the years (one has come here 15 years on the trot!!). their

                              arrival dates obviously change and that's why we work with arrival dates.

                              Also makes a planning list in arrival date order more simple to produce.

                              The fact that your database is of very small "scale" may make it look like you can get away with less than the best design, and that is true to some degree, but if you match by arrival date, you are still setting up your system for problems if you do NOT use arrival date as your match field. Consider the possible problems if a client books a stay, then latter changes it to a different date. When you link records by arrival date, you have to update this field for matching records on both sides of the relationship. Miss one or make a mistake and records do not link correctly. The same is true if you make a mistake specifying the date and fail to immediately notice the mistake. It really is better to match records by Booking ID instead. This does not keep you from finding records by arrival date if you choose, you just don't link the records by this value.

                               

                              And apologies, but I just don't have the time or energy to download your file at this time. Have some paying customers and my regular employer that need my attention....

                              1 of 1 people found this helpful
                              • 13. Re: Field data disappears when switching layouts on same table.
                                BruceRobertson

                                Auto-enter serials everywhere.

                                No relationships.

                                Oh dear.

                                Serious need for training. Or just pay a developer.

                                Booking DB Graph.png

                                Booking DB Bookings.png

                                Booking DB Cashbook.png

                                Booking DB Contacts.png

                                • 14. Re: Field data disappears when switching layouts on same table.
                                  AitchB

                                  Pretty obvious really that I need training. Half the fun is teaching myself

                                  bit by it. But then don't complain if you offer a free service, albeit

                                  limited.

                                  1 2 Previous Next